mysql基本语法总结

新建数据库

1
2
3
4
CREATE DATABASE <数据库名字>;
show databases;
use 数据库名;

数据库建表

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
create table employee(
id int(4) unique,
name varchar(20),
age int(4) default 15,
gender enum('male','female'),
salary int
);
insert into employee values (1,'fang',23,1);//male
insert into employee values (2,'fang',23,2);//female
create table employee(
id int(4) primary key not null auto_increment,
name varchar(20),
age int(4),
salary int
);
create table employee(
id int(4) primary key not null auto_increment,
name varchar(20),
age int(4) defalut ,
salary int
);
create table employee(
id int(4),
name varchar(20),
age int(4),
salary int,
primary key(id)
);
---------------------------
create table student(
sid INT(10) PRIMARY KEY,
sname CHAR(20),
gender ENUM('male','female')
);
CREATE TABLE course
(
cid INT(10) PRIMARY KEY,
cname char(20)
);
外键约束
CREATE TABLE mark
(
mid INT(10) PRIMARY KEY,
sid INT(10),
cid INT(10),
score INT(10),
CONSTRAINT mark_sid_fk FOREIGN KEY(sid) REFERENCES student(sid),
CONSTRAINT mark_cid_fk FOREIGN KEY(cid) REFERENCES course(cid)
);

表查询

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
99
100
101
102
103
104
show column from student;
select name,age from employee;
select name,age from employee where age>25;
select name,age,phone from employee where name='mary';
select name,age from employee where age<25 or age>30;
select name,age from employee where age>25 and age<30;
select name,age from employee where age between 25 and 30;
select name,age,in_dpt from employee where in_dpt in ('dpt3','dpt4');
select name,age,in_dpt from employee where in_dpt not in ('dpt1','dpt3');
_代表任意一个未指定的字符,%代表不定个未指定的字符
select name,age,phone from employee where phone like '110__';
select name,age,phone from employee where name like 'J%';
select name,age,in_dpt from employee where in_dpt in ('dpt3','dpt4');
降序排列
SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
升序排列,默认也是升序
SELECT name,age,salary,phone FROM employee ORDER BY salary ASC;
SQL内置函数和计算
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
----------------------------------------------------
以下两句等价 内连接
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
------------------------------------------------------
外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
1)LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
2)RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
3)FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
3、交叉联接
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。用 CROSS JOIN
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。
例子:
-------------------------------------------------
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
1) 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2)左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3) 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4) 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null    3 34 4
3 王武 null

数据库的修改

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
删除数据库
DROP DATABASE test_01;
重命名表名
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;
删除表
DROP TABLE 表名字;
表中增加字段
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或: ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
增加字段在一地列
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
增加字段在age字段后
ALTER TABLE employee ADD weight INT(4) DEFAULT 120 after age;
删除某一列属性
ALTER TABLE 表名字 DROP COLUMN 列名字;
或: ALTER TABLE 表名字 DROP 列名字;
重命名一列
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
ALTER TABLE 表名字 CHANGE height shengao int(4) default 170;
改变数据类型
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
表中数据修改
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
删除表的一行记录
DELETE FROM 表名字 WHERE 条件;

数据库的载入以及备份导入导出

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
数据导入恢复
source /home/my.sql
创建索引
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);
ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引
CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引
创建视图
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
CREATE VIEW 视图名(v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;
数据导入
LOAD DATA INFILE '文件路径' INTO TABLE 表名字;
数据导出
SELECT 列1,列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
SELECT * INTO OUTFILE '/tmp/out.txt' FROM employee;
非mysql命令行下数据库备份
mysqldump -u root 数据库名>备份文件名; #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名; #备份整个表
mysqldump -u root mysql_shiyan > bak.sql;
数据库恢复
source /tmp/SQL6/MySQL-06.sql
mysql -u root #因为在上一步已经退出了MySQL,现在需要重新登录
CREATE DATABASE test; #新建一个名为test的数据库
mysql -u root test < bak.sql
坚持原创技术分享,您的支持将鼓励我继续创作!