mysql命令

mysqldump 导出数据库       通常启用选项 --single-transaction 来保证备份的一致性  -B , --databases           使用此参数后，导出的SQL文件中会有一行 use `database`;  --no-data , -d             导出数据库结构 不写表的任何行信息。如果你只想转储表的结构这很有用--ignore-table=owl.user    导出时忽略表user  -v, --verbose              打印有关的各个阶段的信息  例:   a 导入数据库1) mysql -u root -p -h 127.0.0.1 owl < /usr/owl.sql 导入文件比较大时不容易观察导入过程2) mysql> source /usr/owl.sqlb 导出所有数据库mysqldump -uroot -p -h127.0.0.1 --all-databases >/root/mysql.sqlc 导出数据库vod smsmysqldump -uroot -p -h127.0.0.1 --single-transaction -B vod sms >/root/mysql.sql   d 导出表  1) mysqldump     owl         t_user > table.sql  2) mysqldump  -B owl --table t_user > table.sql  e 导出表结构   mysqldump  -d owl t_user > table.sql3: 创建UTF8 字符集数据库        create database `owl` default character set utf8    collate utf8_general_ci; 创建UTF8mb4 字符集数据库     create database `owl` default character set utf8mb4 collate utf8mb4_unicode_ci; 数据库名称引号要用Esc下的引号 4:查看现在的数据库中存在什么表mysql> show tables;5:创建一个数据库表mysql> create table mytable (name VARCHAR(20), sex CHAR(1));6:显示表的结构：mysql> describe mytable;查看表内容select * from table7:往表中加入记录mysql> insert into MYTABLE values (”hyq”,”M”);8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;9:导入.sql文件命令（例如 /mysql.sql）mysql>use database;mysql>source /mysql.sql;10:删除表mysql>drop table mytable;11:清空表mysql>delete from mytable;12:更新表中数据mysql>update MYTABLE set sex=”f” where name=’hyq’;查看mysql默认字符集show variables like '%char%';show variables like 'collation_%';查看数据库字符集show create database `supervod`;show variables like  'character_set_%';修改字符集mysql>alter database dbname character set utf-8;更改数据库的字符编码alter database db_name default character set utf8 collate utf8_general_ci查看mysql用户SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;创建test用户，密码123456create user liluguang identified by '123456';Mysql修改密码mysqladmin -u root password 'new-password'/usr/bin/mysqladmin -u root -h novel password 'new-password'删除用户use mysql;delete from user where User="test" and Host="localhost";-----------------------------------------------------------------------------------------------------------------------------------授权test用户从任意地点访问grant all privileges on *.* to test@'%' identified by "test";授权test从218.240.129.156访问grant all privileges on *.* to 'test'@'218.240.129.156' identified by 'password' with grant option;flush privileges;-----------------------------------------------------------------------------------------------------------------------------------查看mysql现在已提供什么存储引擎:mysql> show engines;看你的mysql当前默认的存储引擎:mysql> show variables like '%storage_engine%';你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):mysql> show create table 表名;mysql5.6创建用户INSERT INTO user(host,user,password,select_priv,insert_priv,update_priv,delete_priv,ssl_cipher,x509_issuer,x509_subject)VALUES('%','test',password('123456'),'Y','Y','Y','Y','','','');清除mysql连接for i in $(mysql -uroot -p123456 -Bse "show processlist" | awk '{print $1}');do mysql -uroot -p123456 -e "kill $i";done取最大值select max(a_small) from t_user;最长字段长度 区别：length计算字符长度，lengthb计算字节长度。length:是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符char_length:不管汉字还是数字或者是字母都算是一个字符select max(length(hardware)) from t_client_info;查看总行数和id=8匹配的行数select count(*),sum(user_id=18) from t_media_asset;修改zabbix密码为1234567update users set passwd=md5("1234567") where userid=1;