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
坚持原创技术分享，您的支持将鼓励我继续创作！