1 数据库操作2 create database db_name charset utf-8;#创建数据库3 drop database db_name;#删除数据库4 use db_name;#切换数据库5 show database;#查看所有数据库6
7
8 表操作:9 创建表:10 数据类型:11 整型:12 tinyint smallint int bigint
13 浮点型:14 float double
15 字符型:16 char varchar text
17 日期类型:18 date datatime timestamp
19
20
21 约束:22 1、主键约束 唯一、非空 primary key
23 2、外键约束 foreign key
24 3、唯一约束 unique
25 4、非空约束 not null
26 5、默认值约束 default
27 6、自增长 auto_increment28 DDL、DCL、DML语句:29 DDL:30 数据定义语句 creat drop alter
31 DML:32 数据操纵语句 select insert updata delete
33 DCL:34 数据控制语句 grant
35
36
37 建表:38
39 create tablestudent (40 id int primary keyauto_increment,41 name varchar(20) not null,42 phone varchar(11) unique not null,43 sex tinyint default 0,44 addr varchar(50),45 brith datetime default current_timestamp,46 index(name)47 );48
49 create tablescore (50 id int primary keyauto_increment,51 score float not null,52 sid int not null
53 );54 create table student_new like student; --快速创建一个和student表结构一样的表
55
56
57 修改表:58 alter table student add class2 int not null; --增加字段
59 alter table student drop addr;--删除字段
60 alter table student change name new_name varchar(20) not null;--修改字段
61 alter table student modify name varchar(30);--修改字段属性
62 alter table student add primary key(id);--添加主键
63 alter table student drop primary key(id);--删除主键,非auto_increment类型
64 auto_increment类型删除主键:65 alter table student modify id int;--去掉auto_increment类型
66 alter table student drop primary key(id);--删除主键
67
68 删除表:69 drop tablestudent;70 清空表:71 truncate table student;---自增长id会重新开始
72 其他操作:73 show tables;--查看当前所有表
74 show create table student;--产看建表语句
75 desc student;--查看建表结构
76
77
78 数据操作:79 增:80 insert into student values ('','python','11111111111',0,'北京','2019-01-03 18:39:23'); --写全
81 insert into student (name,phone) values ('mysql','12345678901'); --指定字段
82 insert into student (name,phone) values ('mysql1','12345678902'),('mysql2','22345678901'); --多条
83 insert into student_new select * from student; --把一个表的数据快速导出到另外一个表
84
85 删:86 delete from student; --整表数据删除
87 delete from student where id = 3; --指定数据删除
88
89 改:90 update student set name='mysql3' ; --修改全表数据
91 update student set name='mysql2',sex=1; --修改多个字段
92 update student set name='mysql3' where id = 1; #指定修改某条数据93
94 查:95 基本查询:96 select * from student;--查整表
97 select id,name,addr from student; --指定字段
98 select id as 编号, addr 地址 , name 姓名 from student; --字段加别名
99
100 where条件101 select * from student where id=1; --where条件 >,=,<=,!=,<>
102 select * from student where id in (1,2,3) and id != 5; --in和and条件
103 select * from student where id between 1 and 5; --范围
104 select * from student where id between 1 and 5 or id > 10; --or
105
106 排序107 select * from student where id between 1 and 5 order by id desc; --一起用的话,order by必须写在where条件后面
108 select * from student order by id desc ; --降序排序,按照id,升序的话是asc
109 select * from student order by id,name asc ; --升序,按照id和name排序,asc可以省略不写
110
111 分组112 select * from student group by sex; --按照某个字段分组,可以写多个字段
113 select * from student group by sex having addr !='北京';114 select * from student where id >5 group by sex having addr !='北京'; --如果有where条件,必须写在group by前面,group by后面不能再写where条件,如果有条件必须用having子句
115
116 limit117 select id as 编号, addr 地址 , name 姓名 from student limit 2; --前N条数据
118 select id as 编号, addr 地址 , name 姓名 from student limit 1,5; --从第一行开始,向后取5行,不包含第一行的数据
119 select * from student where id >0 group by sex having addr !='北京' limit 5; --limit必须写在最后面
120 select * from student where id >0 group by sex having addr !='北京' order by id desc limit 5; --limit必须写在最后面
121 #如果一个sql里面有where、group by、排序、limit,顺序一定是1、where 2、group by 3、order by 4、limit122
123 聚合函数124 select count(*) from student; --多少条数据
125 select count(addr) from student; --某个字段不为空的有多少条
126 select count(*) 人数 ,sex 性别 from student group by sex; --多少条数据
127 select avg(age) from student; --平均值
128 select sum(score) from score; --和
129 select min(score) fromscore;130 select max(score) fromscore;131
132 子查询133 select * from student where id in (select sid from score where score >= 60);134
135 多表查询136 select * from student a ,score b where a.id = b.sid and a.score>90;137
138 select a.name,b.score,a.class2 from student a ,score b where a.id = b.sid and a.score>90;--内连接,省略inner join
139
140 select a.name ,b.score,a.class2 from student a inner join score b on a.id = b.sid where a.score > 90;--内连接,inner join
141
142 #左连接,left join
143 #定义:LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。144 #语法: SELECTcolumn_name(s)145 FROMtable1146 LEFT JOINtable2147 ON table1.column_name=table2.column_name;148
149 select a.name ,b.score,a.class2 from student a left join score b on a.id = b.sid where a.score > 90;--左连接,left join
150
151
152 #右连接,right join
153 #定义:RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。154 #语法: SELECTcolumn_name(s)155 FROMtable1156 RIGHT JOINtable2157 ON table1.column_name=table2.column_name;158
159 select a.name ,b.score,a.class2 from student a left join score b on a.id = b.sid where a.score > 90;--左连接,left join
160
161
162
163 授权164 GRANT ALL privileges ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456';165 GRANT ALL privileges ON byz.* TO 'byz'@'%' IDENTIFIED BY '123456';166 flush privileges;