1.DDL 数据库定义语言 data definition language || create alter drop
2.DCL 数据库控制语言 data control language || grant rollback commit
3.DML 数据库操纵语言 data manipulation language || insert delete update select
MySQL常用语句
链接数据库服务
mysql -u root -p
根据提示再输入密码
DDL(create drop alter)
1.创建一个库
create database dname [character set sname collate cname];
character set: 数据库码表
collate: 校对集 决定数据排序
2.查看所有库
show databases;
3.删除一个数据库
drop database dname;
4.修改数据库码表和字符校对
alter database dname character set sname [collate cname];
5.当前要使用的库
use dname;
6.查看当前选择的数据库
select database();
7.显示创建库的语句
show create database dname;
8.创建表
create table name(
filed1 datatype constraint,
filed2 datatype constraint
);
create table table_user(
id int auto_increment,
name varchar(20),
sal double(4, 3),
birthday datatime,
hiredate timestamp
);
列的约束:not null,unique,primary key,auto_increament
9.查看当前库中有哪些表
show tables;
10.查看表的结构
desc tname;
11.添加一列
alter table tname add name;
12.修改列的名称
alter table tname change oname nname datatype;
13.删除列
alter table tname drop name;
14.修改表的名称
rename table oname to nname;
15.修改字符集
alter table tname character set sname collate cname;
16.修改列的数据类型
alter table tname modify rname ndatatype;
DML(insert delete update select)
1.插入一条数据
insert into tname(filed1, filed2) values(v1, v2);
insert int tname values(v1, v2, v3...);
2.修改一条记录
update tname set rname = v1 rname = v2 ... where ...;
3.删除表中的记录
delete from tname where ...;
delete from tname;
truncate table tname;
4.查询语句
select selection_list
from table_list
where condition
group by grouping_columns
having condition
order by sorting_columns
limit offset_start, row_count;
条件判断: and,or,not,in,not in,between and,is not,is not null,like
%:通配任意字符
_:通配一个字符
distinct:去除重复记录
ifnull(arg1,arg2):arg1为空则返回arg2
asc:升序
desc:降序
count(r...):指定列不为NULL的记录数
max(r):指定列最大值
min(r):指定列最小值
avg(r):指定列平均值
example:
select * from stu where gender = 'female' and age < 25;
select * from stu where sid = 'S_1001' or name = 'LiShi';
select * from stu where sid in ('S_1001', 'S_1002');
select * from stu where age is null;
select * from stu where not gender = 'male';
select * from stu where sname = '李%';
select distinct gender from stu;
select sal*12 + ifnull(comm, 0) as '年收入' from stu;
select * from stu order by age asc;
select count(*) from emp where sal + ifnull(comm, 0) > 2500;
select deptno,count(ename) from emp where sal>1500 group by deptno ;
select * from emp limit 5, 5;