(1)删除空行
mysql> delete from qq where id is null;
(2)查看MySQL下所有用户及权限
select distinct concat ('user: ''',user,'''@''',host,''';') as query from user;
(3)查看MySQL中具体某个用户的权限
mysql> show grants for 'backup'@'2.2.2.2';或
mysql> show grants for 'qq'@'%';
(4)查看数据库中所有用户
mysql> select user,host from user;或
mysql> select user,host from user\G;
(5)查看当前连接到数据库的所有用户
mysql> show processlist;
(6)设置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'XINyang3009@@';
(7)跳过密码验证:
skip-grant-tables
(8)查看库:
mysql> show databases;
(9)创建库:
mysql> create database DB1;
(10)删除库:
mysql> drop database DB1;
(11)使用库:
mysql> use DB1;
(12)查看创建库的SQL语句
mysql> show create database DB1\G
(13)查看库中有哪些表:
mysql> show tables;
(14)查看当前处于哪一个数据库:
mysql> select database();
(14)创建表:
create table T1(id int(11),name char(50),sex enum('man','woman'),aihao varchar(50));
mysql> show create table T1\G
mysql> desc T1; 描述表T1并显示表结构
(15) 增加列:
mysql> alter table T1 add salary int;
(16) 删除列:
mysql> alter table T1 drop salary;
(17) 修改列名:
mysql> alter table T1 change aihao pihao varchar(30);
(18) 修改列类型:
mysql> alter table T1 modify pihao char(50);
change 可以修改列明和列类型
modify 只能修改列类型,不能改列名
(19)删除表
mysql> drop table t1;
(20)增加数据:
mysql> insert into T1 values(1,'dongxu','man','AV');
mysql> select * from T1; //查看表中所有的数据
(21)修改数据
mysql> update T1 set pihao='lu' where id=1;
(22)删除数据
mysql> delete from endpoint where id = '1';
(23)查询数据
1)mysql> select * from T1 where id=1;
2)mysql> select * from T1;
3)查询表中某些列的数据
mysql> select id,name,pihao from T1;
(24)创建实验用表:
CREATE TABLE `chengji` (
`id` int(11) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
`name` char(50) DEFAULT NULL,
yuwen` int(11) DEFAULT NULL,
shuxue` int(11) DEFAULT NULL,
`yingyu` int(11) DEFAULT NULL
) DEFAULT CHARSET=latin1;
CREATE TABLE `banji` (
`class` int(11) DEFAULT NULL,
`banzhuren` char(50) DEFAULT NULL,
`id` int(11) DEFAULT NULL
) DEFAULT CHARSET=latin1;
(25)插入数据
mysql> insert into chengji values(1,1,'dongxu',2,15,26),(2,2,'runiu',80,8,0),(3,1,'xiaojingling',100,90,99),(4,1,'haoge',75,71,73),(5,2,'dahuang',0,89,59),(6,4,'dongjiaoshou',79,89,99);
(26)查看不重复的记录:
mysql> select distinct class from chengji;
(27)查看数据库大小
进入information_schema 数据库(存放了其他的数据库的信息),进入到这里查询其他数据库的大小
mysql> use information_schema;
1)查询所有数据的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
2)查看指定数据库的大小(这里以mysql库为例)
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='mysql';
3)查看指定数据库的某个表的大小(mysql库下user表)
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='mysql' and table_name='user';
(28)远程连接:
mysql -h 192.168.13.44 -P 3306 -u slave -p