数据库

一、启动和关闭mysql服务

windows下 

启动 

（方法1）管理员权限进入cmd，执行 net start mysql 

（方法2）管理员权限进入cmd，执行 mysqld -uroot --console 

（方法3）在计算机——服务——mysql，通过图形界面的方式启动 

关闭 

（方法1）管理员权限进入cmd，执行 net stop mysql 

（方法2）管理员权限进入cmd，执行 mysqladmin -uroot shutdown 

（方法3）在计算机——服务——mysql，通过图形界面关闭

linux下 

启动 

（方法1）终端下执行 cd mysql可执行文件目录，比如cd /usr/bin，执行 

./mysqld_safe & 

（方法2）终端下执行 service mysql start （这种方法需要RPM方式安装的mysql才行）

关闭 

（方法1）终端下执行 mysqladmin -uroot shutdown 

（方法2）service stop mysql （这种方法需要RPM方式安装的mysql才行）

常见的异常 

    启动时候失败，提示 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode。解决方案： 

（1）打开任务管理器，终止mysqld.exe 

（2）打开mysql安装目录的data文件夹，删除 ib_logfile0, ib_logfile1 

（3）重启mysql

二、 连接数据库

（方法1）通过mysql workbench图形界面的方式连接 

（方法2）在命令行下，执行 mysql -u 用户名 -p 密码 -h 主机ip，如 

mysql -u root -p,回车后输入密码，不写 -h 表示使用本机ip地址。

三、 SQL语句分类

    mysql支持标准sql语句，同时有自己的拓展语句。SQL语句可以分为三类： 

（1）DDL(Data Definition Language) 数据定义语句，定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包含create、drop、alter等。 

（2）DML(Data Manipulation Language) 

数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update、select。 

（3）DCL(Data Control Language) 

数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别，主要的语句关键字包含grant、revoke等。

    DML语句只是对数据库表内的数据进行操作，而DDL语句可以对表定义、结构进行修改。 

    在mysql命令行下，语句以;作为结束，每个语句之后；然后回车，才会被执行。

四、 DDL语句操作

（4.1）创建数据库 

create database dbname; 

（4.2）显示当前数据库 

show databases; 

    在安装mysql时，会自动创建4个数据库： 

(a)information_schema: 主要保存了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息。 

(b)cluster: 存储了系统的集群信息 （集群就是指一组计算机）

(c)mysql: 存储了系统的用户权限信息 

(d)test: 系统自动创建的测试数据库，任何用户都可以使用

(4.3)选择数据库 

use dbname; 

之后的操作，都是在选择的数据库下的操作。比如，show tables;，显示该数据库下的所有表。

(4.4)查看当前信息 

status,显示当前connection的信息，比如使用的数据库、用户、数据库的字符集等。

（4.5）删除数据库 

drop database dbname; 

在mysql中，drop语句的操作结果均显示'0 rows affected'

(4.6)创建表 

create table tbname( 

column1_name column1_constraints, 

column2_name column2_constraints, 

...

    例如，创建一个emp表，表中包括 ename（姓名）、hiredate（雇佣日期）、sal（薪水）、deptno部门编号三个字段，字段类型分别为varchar(10), date, decimal(10,2), int(2) 

create table emp( 

ename varchar(10), 

hiredate date, 

sal decimal(10, 2), 

deptno int(2)) 

创建数据库和数据表需要注意：

Mysql创建database时指定字符集，防止字符混乱的情况发生：

CREATE database testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

规范建表方式：

CREATE TABLE runoob_tbl(

-> runoob_id INT NOT NULL AUTO_INCREMENT,

-> runoob_title VARCHAR(100) NOT NULL,

-> runoob_author VARCHAR(40) NOT NULL,

-> submission_date DATE,

-> PRIMARY KEY ( runoob_id )

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

(4.6) 查看表定义 

desc tbname;,desc 可以查看表定义，但是输出信息不够全面，可以通过 show create table tbname \G;来查看更加详细的信息，比如存储引擎，字符集等。"\G"选项是使得记录能够按照字段竖向排列，以便更好的显示内容较长的记录。

(4.7)删除表 

drop table tbname;

(4.8)修改表 

    大多数情况下，表结构的修改都通过alter table语句，常用的操作包括： 

<1> 修改字段 (modify 修改)

alter table tbname modify column_name column_definition [first|after col_name] 

比如 alter table emp modify ename varchar(20); #将enmae列类型修改为varchar(20) 

<2> 增加字段 

alter table tbname add column_name column_definition [first|after col_name] 

比如 alter table emp add age int(3) #在emp表中添加列age 

<3> 删除字段 

alter table tbname drop column_name 

比如 alter table emp drop age; 

<4> 字段改名 

alter table tbname change old_col_name new col_name [new_col_definition] [first|after col_name]#[]表示其包含的内容可有可无 

比如 alter table emp change age age1 int(4);#将age字段改名为age1，同时修改字段类型为int(4) 

    change和modify都可以修改表的定义，不同的是change后面需要写两次列明，但是change可以修改列名称，而modify不能。 

<5> 修改字段排列顺序 

上面的add/change/modify操作语句中都有 [first|after col_name]选项，用来修改字段在表中的位置，add新加的字段默认是加在表的最后位置，而change/modify默认都不会改变字段的位置。 

比如 alter table emp add birth date after ename; #将新增的birth字段加在ename之后 

alter table emp modify age int(3) first; #将字段age放在最前面 

<6> 更改表名 

alter table tbname rename new_name

五、 DML语句

(5.1)插入记录 

insert into tbname (field1, field2, ...fieldn) values (val1, val2, ...valn) 

也可以不用指定字段名称，但是values后面的顺序应该和表中字段顺序一致，含可控字段、非空但是含有默认值的字段、自增字段，可以不用在insert后的字段列表里面出现，values后面只写对应字段名称的value。 

 insert语句可以一次性插入多条记录，格式为： 

·insert into tbname (field1, field2, ...fieldn) values 

(val11, val12, ... val1n), 

(val21, val22, ... val2n), 

.... 

(valk1, valk2, ... valkn)

(5.2)更新记录 

update tbname set col1 = val1, col2 = val2 .. [where condition] 

    在mysql中，update命令可以同时更新多个表中的数据，格式如下： 

update t1, t2, ...tn set t1.field1=expr1, ... tn.fieldn=exprn [where condition] 

比如 udpate emp a, dept b set a.sal=b.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno;#emp表（记为a）和dept表（记为b），a中存放雇员信息，包括雇员薪水和其所在的部门号；b中存放部门信息，包括部门号和部门名称。对于给出a的一条雇员信息记录，通过其部门号deptno，从b中找到该deptno对应的deptname.....

(5.3)删除记录 

    如果记录不再需要，可以用delete命令删除 

delete from tbname [where condition] 

在mysql中可以一次删除多一个表中的数据，格式如下： 

delete t1,t2....tn from t1,t2...tn [where condition] 如果from后面的表名为别名，则delete后面也要用相应的别名，否则会提示语法错误。 

例如：delete a,b from emp a, dept b where a.deptno = b.deptno and a.deptno =3;

无论是单表还是多表，如果不加where条件，会将表的所有记录删除。

(5.4)查询记录 

select field1, field2,....fieldn from tbname [where condition] 

<1> 普通查询 

比如： 

select age, deptno from emp where sal > 1000 and deptno = 1; 

select * from emp; 

<2> 查询不重复的记录 

有时候需要将表中重复的记录去掉后显示出来，可以用distnict关键字来实现： 

select distinct deptno from emp; 

<3> 排序和限制 

如果需要返回按照某个字段排序后的结果集，可以使用order by实现： 

select * from tbname [where condition] order by field1 [desc|asc], 

field2 [desc|asc] ... fieldn[desc|asc] 

limit：限制从第三条记录开始的两条记录

例如：select *from emp order by sal limit 2,2 ;

排序规则默认为asc（升序排序）

(5.4)聚合 

 对数据进行汇总操作，group by 

select [field1, field2 ... fieldn] fun_name from tbname [where where_condition] 

[group by field1, field2....fieldn] [with rollup] [having where_condition]

fun_name 表示要做的聚合操作，即聚合函数，常用的有sum，count，max，min，avg等 

group by关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面 

with rollup 可选语法，表明是否对分类聚合后的结果进行再汇总 

having 关键字表示对分类后的结果再进行条件的过滤 

having和where的区别：having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能使用where先过滤记录，这样减少结果集，提高效率。

比如： 

select count(1) from emp; 统计公司雇员人数 

select deptno, count(1) from emp group by deptno; 统计各个部门的人数 

select deptno, count(1) from emp group by deptno with rollup;统计各部门人数，最后进行汇总 

select deptno, count(1) from emp group by deptno having count(1) > 1;统计部门人数大于1的各个部门以及该部门的人数 

select max(val), min(val), sum(val) from emp; 统计所有的雇员中的最大、最小、总薪水值

(5.5)表连接 

    当需要同时显示多个表中的字段，需要用到表连接。表连接分为内连接和外连接，之间的主要区别是，内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录，常用内连接。 

比如： 

内连接select ename, deptname from emp, dept where emp.deptno = dept.deptno; 

外连接分为左连接和右连接，左连接是指包含所有的左边的表中的记录，即使有些记录在右边的表中没有与之相匹配的记录； 

右连接是指包含所有的右边表中的记录，即使有些左边表中没有与之匹配的记录。 

比如： 

select ename, deptname from emp left join dept on emp.deptno=dept.deptno;emp表左连接dept表，连接后的结果中包含emp表中的所有的ename，即使该ename对应的员工的deptno在dept表中不存在。

(5.6)子查询 

    某些情况下，当进行查询的时候，需要的条件就是另外一个select的结果，这个时候需要用到子查询。用于子查询的关键字主要包括 in, not in, =, !=, exists, not exists等。 

比如： 

select * from emp where deptno in (select deptno from dept);只查找deptno在dept表中存在的那些deptno的emp中的记录。 

    如果子查询记录数唯一，还可以用 = 代替 in。 

    某些情况下，子查询可以转化为表连接，例如： 

select * from emp where deptno in (select deptno from dept); 

转化为表连接之后，为 select emp.* from emp, dept where emp.deptno=dept.deptno;

(5.7)记录联合 

    如果需要将两个表的数据按照一定的查询条件查询出来以后，将结果合并到一起显示出来，此时需要用union和union all关键字来实现这样的功能： 

select * from t1 union|union all 

select * from t2 union|union all 

.... 

select * from tn; 

例如：

select deptno from emp

union all

select deptno from dept;

 

    union和union all的区别在于：union all是把结果集直接合并在一起，而union是将union all的结果进行一次distinct，去除重复记录后的结果。

    比如，将emp和dept表中的部门编号的集合显示出来： 

select deptno from emp union all select deptno from dept;这样将显示所有的在emp表和deptno表中出现的deptno，

去掉重复：select deptno from emp union select deptno from dept;

六、DCL语句

    DCL语句主要是DBA用来管理系统中的对象使用权限时使用，一般的开发人员很少使用。 

    比如创建一个数据库用户zl，具有对sakila数据库中所有表的select/insert权限： 

mysql -u root -p 

grant select,insert on sakila.* to 'zl'@'localhost' identified by '123';此时，数据库sakila多了一个新的用户zl，密码为123，且具有select，insert权限。 

    由于权限变更，需要将zl的权限修改，收回insert，只能对数据进行select操作： 

mysql -u root -p 

revoke insert on sakila.* from 'zl'@'localhost'

七、 查询元数据信息

    msql 5.5之后，提供了新的数据库information_schema，用来记录mysql中的元数据信息。元数据指数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。该数据库是一个虚拟的数据库，物理上不存在相关的目录和文件，通过show tables显示的并不是实际存在的表，全部是视图。 

    一些比较常用的视图： 

schemata：提供了当前mysql示例中的所有数据库的信息，show databases的结果来自于该表； 

tables：提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息。show tables from schemaname的结果来自该表； 

colunms：提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息，show columns from schemaname.tablename的结果取自该表； 

statistics：该表提供了关于表索引的信息

    利用数据库元信息进行一些操作，比如删除数据库 test1 下所有前缀为tmp的表： 

select concat('drop table test1.', table_name, ';') from tables where table_schema='test1' and table_name like 'tmp%';

 

函数：

字符串操作：

数值操作：

时间操作

流程函数

 

NULL 和 NOT NULL 修饰符

可以在每个字段后面都加上这 NULL 或 NOT NULL 修饰符来指定该字段是否可以为空(NULL)，

还是说必须填上数据 (NOT NULL)。MySQL 默认情况下指定字段为 NULL 修饰符，如果一个字段指定为NOT NULL，MySQL 则不允许向该字段插入空值(这里面说的空值都为 NULL)，因为这是 “规定”。

1. /*  
2. 创建好友表，其中id ,name ,pass都不能为空  
3. */  
4. create table friends (  
5. id int(3) not null,  
6. name varchar(8) not null,  
7. pass varchar(20) not null  
8. );  
9.   
10. /*  
11. 错误提示，id列不能为空  
12. #1048 - Column 'id' cannot be null  
13. */  
14. INSERT INTO friends  
15. VALUES (  
16. NULL , 'simaopig', 'simaopig'  
17. );  

但是在自增列和 TIMESTAMP 字段中，这个规则并不适用。

向这些字段中插入 NULL 值将会导致插入下一个自动增加的值或者当前的时间戳。

 

DEFAULT 修饰符

可以使用 DEFAULT 修饰符为字段设定一个默认值。

当插入记录忘记传该字段的值时，MySQL 会自动为您设置上该字段的默认值。

[sql] view plain copy

1. /*  
2. 创建im表，将name字段设置为默认值'QQ'  
3. */  
4. create table im (  
5. id int(3) not null,  
6. name varchar(25) not null default 'QQ'  
7. );  
8.   
9. /*  
10. 插入数据，不传name字段的值，MySQL会为其设置默认值的  
11. 您运行的 SQL 语句已经成功运行了。  
12. */  
13. INSERT INTO im( id, name ) VALUES ( 2, 'MSN' ) ;  
14.   
15. INSERT INTO im( id ) VALUES ( 3 ) ;  
16.   
17. SELECT * FROM im LIMIT 0 , 30;  
18.   
19. /*  
20. id name  
21. 2 MSN  
22. 3 QQ  
23. */  

如果一个字段中没有指定 DEFAULT 修饰符，MySQL 会依据这个字段是 NULL 还是 NOT NULL 自动设置默认值。

如果指定字段可以为 NULL，则 MySQL 为其设置默认值为 NULL。

如果是 NOT NULL 字段，MySQL 对于数值类型插入 0，字符串类型插入空字符串，

时间戳类型插入当前日期和时间，ENUM 类型插入枚举组的第一条。

 

AUTO_INCREMENT 修饰符

AUTO_INCREMENT 修饰符只适用于 INT 字段，表明 MySQL 应该自动为该字段生成一个数

(每次在上一次生成的数值上面加 1)。对于主键非常有用的。

因为其允许开发者使用 MySQL 为每条记录创建唯一的标识符。

[sql] view plain copy

1. /*  
2. 您运行的 SQL 语句已经成功运行了。 ( 查询花费 0.0170 秒 )  
3. */  
4. CREATE TABLE items(  
5. id int( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
6. label varchar( 255 ) NOT NULL  
7. );  
8.   
9. /*  
10. 插入三条数据，不指定id，采用默认值，加上AUTO_INCREMENT  
11. 您运行的 SQL 语句已经成功运行了。  
12. */  
13. insert into items(label) values ('xxx');  
14. insert into items(label) values ('yyy');  
15. insert into items(label) values ('zzz');  
16.   
17. /*  
18. 全显示出来，看一下数据，注意看id的变化  
19. */  
20. select * from items;  
21.   
22. /*  
23. id label  
24. 1 xxx  
25. 2 yyy  
26. 3 zzz  
27. */  

MySQL 表中只能有一个 AUTO_INCREMENT 字段，而且这个字段必须被定义为键。

除了字段的约束，MySQL 也允许表级的约束，比如主键和外键、索引和惟一约束。

 

模糊查询：

SELECT 字段 FROM 表 WHERE 某字段 Like 条件

1、%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'

将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'

若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%' （固定了"三"在前“猫”在后）

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

 

2、_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'

只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

再比如 SELECT * FROM [user] WHERE u_name LIKE '三__';

只找出“三脚猫”这样name为三个字且第一个字是“三”的；

 

3、[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'

将找出“老1”、“老2”、……、“老9”；

 

4、[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';

将排除“老1”到“老4”，寻找“老5”、“老6”、……

 

5、查询内容包含通配符时

由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：

function sqlencode(str)

str=replace(str,"[","[[]") '此句一定要在最前

str=replace(str,"_","[_]")

str=replace(str,"%","[%]")

sqlencode=str

end function

 

exist 和 in 的使用对比：

转自http://sunxiaqw.blog.163.com/blog/static/990654382013430105130443/

exit : exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个boolean条件，当能返回结果集则为true，不能返回结果集则为 false

not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

 

in:in查询相当于多个or条件的叠加

in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后在将子查询条件的结果集分解成m个，再进行m次查询

 

值得一提的是，in查询的子条件返回结果必须只有一个字段，例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就没有这个限制

如果查询的两个表大小相当，那么用in和exists差别不大。 

无论那个表大，用not exists都比not in要快。