mySQL sql语句

C:\Windows\System32\cmd.exe cmd

exit

登陆

mysql -u root -p

显示所有数据库

show databases;

创建数据库

CREATE DATABASE student;

删除数据库

进入数据库

USE student

显示当前数据库的所有表

show tables;

创建表

CREATE TABLE stu_info(
stu_id int not null primary key auto_increment ,
stu_name char(20),

​ stu_sex char(20) null,

​ stu_class char(20) null

);

查看表的创建语句

show create table stu_info;

查看表结构

desc stu_info;

修改表名

alter table stu_info rename stu_info1;

添加表列

alter table stu_info add column chengji varchar(10);

删除表列

alter table stu_info drop column chengji;

修改表列名与类型

//修改表列类型

alter table stu_info modify stu_age varchar(10);

//修改表列名与类型

alter table stu_info change column stu_sex stu_sex1 varchar(30);

删除表

drop table t1;

添加数据

insert into stu_info(stu_id,stu_name,stu_sex,stu_age,stu_class)values(106,“girl”,“nv”,18,“gaosan”);

修改数据

update stu_info set stu_name=“boy”,stu_sex=“nan”,stu_age=22,stu_class=“gaosi” where stu_id=105;

删除数据

delete from stu_info1;//删除表中的所有记录

delete from stu_info where stu_id=101;

查询数据

employees

查询表所有记录 select * from employees;

查询需要的字段（按给定顺序显示）

select employee_id,last_name,first_name from employees;

条件查询

根据某一个字段查询(条件查询)

select * from employees where employee_id=188;

查询某个字段为空

select * from employees where commission_pct is null;

SELECT firsr_name,last_name FROM employees WHERE id=1;

SELECT s.name,s.gender FROM student s where id=1;//使用别名

SELECT first_name FROM employees WHERE salary=12000 and department_id=80;

SELECT * FROM employees WHERE salary>5000;

SELECT * FROM employees WHERE manager_id=100 or salary>8000;

蘑菇查询

SELECT * FROM employees where first_name LIKE "T% 名字以T开头

SELECT * FROM employees where first_name LIKE “%T”; 名字以T结尾

SELECT * FROM employees where first_name LIKE “%T%”;名字中有T

ORDER BY

按department_id降序查询

SELECT * FROM employees ORDER BY department_id desc;

先排序salary，salary相同再按department_id排序

select * from employees order by salary desc,department_id;

SELECT job_id,COUNT(job_id) 人数 FROM employees GROUP BY job_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ON7mzTPs-1597318535974)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812210059714.png)]

HAVING 条件语句一个 HAVING 子句(条件查询)必须位于 GROUP BY 子句之后，并位于 ORDER BY 子句之前

按性别分组，查询出女生人数的总数

SELECT job_id,COUNT(job_id) AS 人数 FROM employees GROUP BY job_id having job_id=‘SA_REP’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w3oKTtWQ-1597318535987)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812210007431.png)]

查询3条记录，从5开始

select * from employees limit 5,3;

查询表中记录总数

select count(*) as 总数 from employees;

事物

事务(Transaction)，也就是要么成功，要么失败，并恢复原状

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q9CuWXV9-1597318535990)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812213649192.png)]原来的表

set autocommit = false;

commit;

savepoint datainit; //设置初始化点1

插入数据之后的表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mjr1MXNy-1597318535992)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812213852682.png)]

savepoint datainit1; //设置初始化点1

修改了110的数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KoK1tvac-1597318535996)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812214040206.png)]

rollback to datainit1;

回到[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tDMiCNFG-1597318535998)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812214146059.png)]

rollback to datainit;

再回到[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nd0FXvjo-1597318535999)(C:\Users\Carson\AppData\Roaming\Typora\typora-user-images\image-20200812214214423.png)]

手动提交每次重启都要打开

不设置初始化点rollback直接返回提交点(中途可以有很多操作);

set autocommit = false –设置事务提交方式为“手动提交”

set autocommit = true –设置事务提交方式为“自动提交”

savepoint datainit;

rollback to datainit;

commit;事物提交