话不多说
mysql -uroot -p
show databases; //查看数据库
show tables; // 查看表
create database HA; //创建数据库
ceate table xixi(id int primary key auto__increment nut null,name varchar(20),age varcahr(20),sex enum('m','f') default m,salary flost(5,2)); //创建表
insert into xixi values(1,'hehe','20',m,'4000.00'),(2,'haha','22',m,'6000.00'),(3,'yezhu','38',m,'15000.00');向表中插入数据
insert into xixi （name,age,sex....）values(1,'hehe','20',m,'4000.00'),(2,'haha','22',m,'6000.00'),(3,'yezhu','38',m,'15000.00');向表指定中插入数据
desc xixi;查看表中结构
explain xixi; 同上
show fields from mysql.user;
show create table xixi\G;
select now();查看当前的时间
select database();
select status;
select user();
select version();
update students set id=2; 所有的都变为2
update students set sex='M' where id=2；
alter table xixi add head varchar(20); ///插入在最后面
alter table xixi add head varchar(20) after age; //插入在age后面
alter table xixi add head varchar(20) first; //插入在最前面
alter table xixi drop sex; 删除sex字段
alter table xixi add modify sex tianyun; //修改sex字段类型
alter table xixi change sex to tianyun varcahr(20);修改sex字段的名字和类型
alter table 表名 rename 新表名;
drop database HA if exists;
create database HA if not exists;
create table huhu select * from xixi; //复制xixi表的结构
create user 'xixi'@'localhost' indetified with mysql_native_password by '密码'; //创建本地用户
create user 'xixi'@'%' indetified with mysql_native_password by '密码'; //创建远程用户
select user,host from mysql.user; //可在user表中查看结构
update mysql.user set host='%' name='xixi'; 更改本地用户为远程用户
update mysql.user set host='192.168.0.%' name='xixi'; 更改用户为内网网段访问
alter user 'root'@'localhost' identified with mysql_native_password by '新密码';更改用户的密码
alter user 'root'@'%' identified with mysql_native_password by '新密码';
//create database xixi;use xixi;
删除表
delete from mysql.user;
delete from students where id=3;
delete from students where age is null;
去重
select distinst name,age from xixi;
select distinct id,name,age from xixi where id=3;
select * from user where age >20 and salary>5000;
or和and 同时存在时，先算and的两边值,逻辑与先执行
select id,name,age from xixi where id>3 and age>25;
别名
select name,salary,salary*14 from haha;创建别名
select name,salary,salary*14 AS gongzi from haha;别名
select CONCAT(name,’annual ‘salary:’,salary*14) AS annual_salary from example;
where salary BETWEEN 5000 AND 15000; //BETWEEN and 多少到多少
select name,salary from employee5 where comment is null;空
select name,salary from employee5 where comment is not null;非空
select name,salary from employee5 where salary=4000 or salary=5000 or salary=20000;
查找工资是4000或者五千或者两万的
IN关键字
select name,sakary example where salary in (4000,5000,20000); 查找工资是4000或者五千或者两万的
select name,sakary example where salary not in (4000,5000,20000);查找工资不是是4000或者五千或者两万的
关键字like模糊查找
select * from example where name like ‘al___’; 一个下划线代表任意一个字符
select * from example where name like ‘al%’; %代表任意多个字符
ASC升序 DESC降序
select name,salary example where order by salary DESC,comment ASC;
grant select,create,insert,update on xixi.* to ‘xixi‘@’10.0.0.%’ identified by ‘123’;
grant * on . to ‘xixi‘@’%’ identified by ‘密码’；
revoke：回收权限
REVOKE INSERT ON . FROM clsn@localhost;
revoke drop,delete on . from sys@localhost;
select * from example order by name, salary ASC limit 2,2;从第二个取值
select * from example order by name, salary ASC limit 2;取值
mysql> select max(sex) from server where age=10;
+———-+
| max(sex) |
+———-+
| 7 |
+———-+
1 row in set (0.00 sec)
mysql> select count(*) from server where age=10;
+———-+
| count(*) |
+———-+
| 3 |
+———-+
1 row in set (0.00 sec)
mysql> select max(sex) from server where age=10;
+———-+
| max(sex) |
+———-+
| 7 |
+———-+
1 row in set (0.00 sec)
mysql> select min(sex) from server where age=10;
+———-+
| min(sex) |
+———-+
| 6 |
+———-+
1 row in set (0.00 sec)
mysql> select avg(sex) from server where age=10;
+——————-+
| avg(sex) |
+——————-+
| 6.333333333333333 |
+——————-+
1 row in set (0.00 sec)
mysql> select sum(sex) from server where age=10;
+———-+
| sum(sex) |
+———-+
| 19 |
+———-+
mysql> select sex,GROUP_CONCAT(rnname) from server group by sex;
+——+———————-+
| sex | GROUP_CONCAT(rnname) |
+——+———————-+
| 6 | lanzi,gouzi |
| 7 | daqiang |
+——+———————-+
