MySQL数据库sql语句的用法总结

###一、基本语句

  • b<=a && a <= c 的SQL语句：
    表达式”a BETWEEN b AND c”等于表达式 “a >= b AND a <= c”,在比较表达式时,a可以是具有任何亲和性。
  • a的值是x,y,z其中一个值：
    “a IN (x, y, z)” 和 “a = z OR a = y OR a = z”视为相等.
  • 如何创建制定目录的数据库：
    sqlite3 D:/Project/SyncML/Lib/debug/atsync.db
    如果不往数据库里面添加任何的表，这个数据库等于没有建立，不会在硬盘上产生任何文件，如果数据库已经存在，则会打开这个数据库。
  • 添加一张数据表：
    create table student(name varchar(10), age smallint);
  • 往数据表中添加数据：
    insert into student values(‘张三’, 20);
  • 在SQLite中，在表上添加或删除一列:
    SQLite 有有限地 ALTER TABLE 支持。你可以使用它来在表的末尾增加一列，可更改表的名称。 如果需要对表结构做更复杂的改变，则必须重新建表。 重建时可以先将已存在的数据放到一个临时表中，删除原表， 创建新表，然后将数据从临时表中复制回来。
    如，假设有一个 t1 表，其中有 “a”, “b”, “c” 三列， 如果要删除列 c ，以下过程描述如何做:
    开始事物处理
    BEGIN TRANSACTION;
    创建临时表格t1-backup
    CREATE TEMPORARY TABLE t1_backup(a,b);
    将数据库表t1中的所有数据拷贝到表t1-backup中
    INSERT INTO t1_backup SELECT a,b FROM t1;
    删除表格t1
    DROP TABLE t1;
    创建表格t1
    CREATE TABLE t1(a,b);
    将数据库表t1-backup中的所有数据拷贝到表t1中
    INSERT INTO t1 SELECT a,b FROM t1_backup;
    删除备份表格t1-backup
    DROP TABLE t1_backup;
    事物提交
    COMMIT;
  • SQLite支持的数据类型:
    NULL 值为NULL
    INTEGER 值为带符号的整型，根据类别用1，2，3，4，6，8字节存储
    REAL 值为浮点型，8字节存储
    TEXT 值为text字符串，使用数据库编码(UTF-8, UTF-16BE or UTF-16-LE)存储
    BLOB 值为二进制数据，具体看实际输入
  • 如果将声明表的一列设置为 INTEGER PRIMARY KEY，则具有：
    1． 每当你在该列上插入一NULL值时， NULL自动被转换为一个比该列中最大值大1的一个整数；
    2．如果表是空的， 将会是1
    注意该整数会比表中该列上的插入之前的最大值大1。 该键值在当前的表中是唯一的。但有可能与已从表中删除的值重叠。要想建立在整个表的生命周期中唯一的键值，需要在 INTEGER PRIMARY KEY 上增加AUTOINCREMENT声明。那么，新的键值将会比该表中曾能存在过的最大值大1

###案例（CRUD）（以下所有中括号[]均视为注释）

  1. 创建一个数据库：create database [数据名]; （…+character set utf8；）
  2. 查看所有的数据库库：show databases;
  3. 删除数据库 drop database [数据名];
  4. 创建表：
    create table [表名（无中括号）]（id integer primary key autoincrement，[列名1] varchar([长度])，[列名2小数类型] double，[列名3大文本类型] text，[列名4日期型] data）;

  5. insert：

    • 向表中插入一条数据：insert into [表名] ([列表1]，[列名2]，[列名3]，[列名4])values('[列表1的值]'，3.14，'xyabc'，2009-07-22')；
      insert a (([列表1]) values('([列表1的值]');
    • 向表中插入多条数据：
      insert into [表名] ([列表1]，[列名2]，[列名3]，[列名4])values('[列表1的值]'，3.14，'xyabc'，2008-08-08')，('[列表1的值]'，3.15，'jzm'，2016-03-15')；
    • 为表增加一列：alter table [表名] add [列名5] blob;
    • 修改[列名1]，使其长度为60：alter table [表名] modify [列名1] varchar(60);
    • 删除[列名3大文本类型]列：alter table [表名] drop [列名3大文本类型];
    • 修改表名：rename table [表名] to [新表名];
    • 修改[列名2]：alter table [表名] change column [列名2] [新列名2] double;
    • 删除一张数据表：drop table [表名];

  6. update:

    • 修改其中一条中一个数据：
      update [表名] set [列表1]='[列表1的新值]' where [列名2]=3.14;
    • 修改其中一条的多个数据：
      update [表名] set [列表1]='[列表1的新值]' ，[列名3]='helloworld' where [列名2]=3.14;
  7. delete:
    • 删除表中[列名2]为’3.14’的记录:
      delete from [表名] where name='3.14';
    • 删除表中所有记录:
      Delete是一条条的删，如果条目很多的话，会效率很低
      删除一个数据表的数据：delete from [表名];
      使用truncate删除表中记录。 摧毁表再创建表 尽量用这个:truncate [表名];
  8. select:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
create table student(
id int,
name varchar(20),
chinese float,
english float,
math float);
insert into student(id,name,chinese,english,math) values(1,'张三',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李四',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'赵六',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'周七',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);
查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select distinct english from student;
在所有学生分数上加10分特长分。
select name,english+10,chinese+10,math+10 from student;
统计每个学生的总分。
select name,english+chinese+math as sum from student;
使用别名表示学生分数。
where 子句
查询姓名为李一的学生成绩
select * from student where name='李一';
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于200分的所有同学
select name,english+chinese+math sum from student where english+chinese+math>200;
此处可以不用as
运算符
查询英语分数在 80－90之间的同学。
select * from student where english between 65 and 85;
查询数学分数为89,90,91的同学。
select name,math from student where math in(89,90,91);
查询所有姓李的学生成绩。
select * from student where name like '李%';
// 查询姓李的两个字的学生
select * from student where name like '李_';
查询数学分>80，语文分>80的同学。
select * from student where math>80 and chinese>80;
查询英语>80或者总分>200的同学
select *,chinese+math+english from student where english>80 or chinese+english+math>200;
order by 子句
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出，然后再按从高到低的顺序输出
select *,chinese+math+english from student order by chinese+math+english desc;
对姓李的学生成绩排序输出 order从句是需要放在where从句的后面
select *,chinese+math+english from student where name like '李%' order by chinese+math+english;
合计函数
count
统计一个班级共有多少学生？
select count(*) from student;
统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;
统计总分大于230的人数有多少？
select count(*) from student where chinese+math+english>230;
sum
统计一个班级数学总成绩？
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
缺考的不参与计算
select sum(chinese)/count(chinese) from student;
avg
语文平均分
select avg(chinese) from student;
max/min
语文最 高分
select max(chinese) from student;

###命令：

  1. 查看数据库有哪些数据表：.tables
  2. 查看数据表的结构：.schema contact
  3. 查看当前的数据库：.database
  4. 导入一个文件到某个表中：.import 文件路径 表名
  5. 设置文件字段的分隔符：.separator “,” 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select语句（6）
group by
订单表
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
将商品归类
select * from orders group by product;
显示单类商品总结
select *,sum(price) from orders group by product;
商品分类 显示单类商品总价大于100的
select *,sum(price) from orders group by product having sum(price)>100;
// 将单价大于20 的商品进行归类显示 按照价格排序
select * from orders where price>20 group by product order by price;

9、表的约束：
表的约束就是在定义表时，我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定
一、表的约束
表的约束就是在定义表时，为表中的字段加一些约束条件，对将来插入的数据做一些限定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
1. 唯一约束 unique
create table a
(
id int,
username varchar(20) unique,
password varchar(20)
);
insert into a (id,username,password) values(1,'zhangsan','1111');
insert into a (id,username,password) values(2,'wangwu','1111');
2. 非空约束 not null
create table b
(
id int not null,
name varchar(20)
);
insert into b (id,name) values (1,'aaaa');
insert into b (id,name) values (1,'bbbb');
3. 主键约束 相当于 唯一约束+非空约束
数据库中的每张表都应该至少有一个主键，通常是id
create table c
(
id int primary key,
name varchar(20)
);
insert into c(id,name) values (1,'aaaa');
insert into c(id,name) values (2,'bbbb');
create table d
(
firstname varchar(20),
lastname varchar(20),
primary key(firstname, lastname)
);
insert into d (firstname, lastname) values ('tom', 'cat');
insert into d (firstname, lastname) values ('tom', 'hks');
create table e
(
id int ,
name varchar(20)
);
insert into e (id,name) values(1,'aaa');
为表加上主键约束
alter table e add primary key(id);
此处修改的命令：
修改job列，使其长度为60。
alter table employee modify job varchar(60);
删除主键约束
alter table e drop primary key;
4. 定义主键自动增长
这个的作用就是让id键随着条目的增加，自动往上增长。
注意主键类型必须是int，只有int类型的才可以自增
create table f
(
id int primary key auto_increment,
name varchar(20)
);
insert into f(name) values ('aaa');
insert into f(id,name) values (11,'bbb');
mysql> select * from f;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 11 | bbb |
| 12 | aaa |
| 13 | aaa |
| 14 | aaa 
| 15 | aaa |
| 16 | aaa |
+----+------+
外键约束
约束力: 插入的外键值必须为被参照列存在的值
被参照表中被参照的数据不允许删除
注意：外键约束并没有非空和唯一的约束力
创建丈夫表和妻子表
create table husband
(
id int primary key auto_increment,
name varchar(20)
);
create table wife
(
id int primary key auto_increment,
name varchar(20),
husbandid int,
constraint husbandid_FK foreign key(husbandid) references husband(id)
);
外界约束：Constraint是定义外键约束的，foreign key是外键的名称 参照某张表的主键
分别添加两条记录
insert into husband(name) values ('laobi');
insert into husband(name) values ('laoyu');
insert into wife(name,husbandid) values ('fengjie',2);
insert into wife(name,husbandid) values ('furongjie',1);
fengjie 找老公
select * from husband where id=(select husbandid from wife where name='fengjie');
delete from husband where name='laoyu';

10 . 表的关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
1 多对一
创建部门表
create table department(id int primary key auto_increment,name varchar(20));
添加三个部门信息
insert into department(name) values('开发部');
insert into department(name) values('销售部');
insert into department(name) values('人事部');
创建员工表
create table employee(id int primary key auto_increment,name varchar(20),departmentid int,
constraint departmentid_FK foreign key(departmentid) references department(id));
添加六个员工信息
insert into employee(name,departmentid) values ('张三',1);
insert into employee(name,departmentid) values ('李四',1);
insert into employee(name,departmentid) values ('王五',2);
insert into employee(name,departmentid) values ('赵六',3);
insert into employee(name,departmentid) values ('田七',3);
insert into employee(name,departmentid) values ('周八',null);
多表的查询
查出1号部门所有的员工
select * from employee where departmentid=1;
查出开发部所有的员工
select * from employee where departmentid=(select id from department where name='开发部');
查出赵六在那个部门
select * from department where id=(select departmentid from employee where name='赵六');
--------------------------------------
联合查询
查出开发部所有的员工
select * from department,employee;
+----+--------+----+------+--------------+
| id | name | id | name | departmentid |
+----+--------+----+------+-------------+
| 1 | 开发部 | 1 | 张三 | 1 |
| 2 | 销售部 | 1 | 张三 | 1 |
| 3 | 人事部 | 1 | 张三 | 1 |
| 1 | 开发部 | 2 | 李四 | 1 
| 2 | 销售部 | 2 | 李四 | 1 |
| 3 | 人事部 | 2 | 李四 | 1 |
| 1 | 开发部 | 3 | 王五 | 2 |
| 2 | 销售部 | 3 | 王五 | 2 |
| 3 | 人事部 | 3 | 王五 | 2 |
| 1 | 开发部 | 4 | 赵六 | 3 
| 2 | 销售部 | 4 | 赵六 | 3 |
| 3 | 人事部 | 4 | 赵六 | 3 |
| 1 | 开发部 | 5 | 田七 | 3 |
| 2 | 销售部 | 5 | 田七 | 3 
| 3 | 人事部 | 5 | 田七 | 3 |
| 1 | 开发部 | 6 | 周八 | NULL |
| 2 | 销售部 | 6 | 周八 | NULL |
| 3 | 人事部 | 6 | 周八 | NULL |
+----+--------+----+------+--------------+
笛卡尔集 多张表的所有记录的排列组合
笛卡尔集当中有很多部匹配的数据(参照表外键列的值和被参照表主键的值不一致) 为了方便 以下称为废数据
加条件去掉废数据
select * from department,employee where employee.departmentid=department.id;
加条件做查询
select * from department,employee where employee.departmentid=department.id and department.name='开发部';
优化 给表加别名 只保留需要的数据
select e.* from department d,employee e where e.departmentid=d.id and d.name='开发部';
查出赵六在那个部门
select d.* from department d,employee e where e.departmentid=d.id and e.name='赵六';
2. 多对多
创建老师表
create table teacher(id int primary key auto_increment,name varchar(20));
添加三个老师
insert into teacher(name) values ('老方'),('老余'),('老毕');
create table student(id int primary key auto_increment,name varchar(20));
添加三个学生
insert into student(name) values('大毛'),('二毛'),('三毛'),('四毛'),('五毛'),('六毛'),('七毛'),('八毛'),('小毛');
创建中间表描述关系
create table tea_stu(teaid int,stuid int,primary key(teaid,stuid),constraint teaid_FK foreign key(teaid) references teacher(id),constraint stuid_FK foreign key(stuid) references student(id));
添加数据
insert into tea_stu (teaid,stuid) values (1,1),(1,2),(1,4),(1,5),(1,6),(1,8),(2,1),(2,3),(2,5),(2,7),(2,9),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7),(3,8);
查询2号老师教过的学生
select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2;
查询老余教过的所有的学生
select s.* from student s,tea_stu ts,teacher t where ts.stuid=s.id and ts.teaid=t.id and t.name='老余';
多表联合查询
查询的结果为笛卡尔集
n张表联合查需要n-1个条件来去掉废数据
去掉废数据的条件 参照表的外键列=被参照表的主键列
再加上查询条件即可得到结果
3. 一对一
create table person(id int primary key auto_increment,name varchar(20));
create table idcard(id int primary key,location varchar(20),
    constraint personid_FK foreign key(id) references person(id));
insert into person (name) values('zhangsan');
insert into person (name) values('lisi');
insert into idcard (id,location) values(2,'天津');
insert into idcard (id,location) values(1,'上海');
查李四的身份证
select idcard.* from person,idcard where idcard.id=person.id and person.name='lisi';