MySql 基本操作

l  DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

基本操作:

查询所有数据库名称: SHOW DATABASES;

切换数据库: USE mydb1, 切换到mydb1数据库;

操作数据库

• 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

• 删除数据库：DROP DATABASE [IF EXISTS] mydb1；

删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

• 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

修改数据库mydb1的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。

数据类型:

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

常用类型：

• int：整型
• double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
• decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题；
• char：固定长度字符串类型；
• varchar：可变长度字符串类型；
• blob：字节类型；
• date：日期类型，格式为：yyyy-MM-dd；
• timestamp：时间戳类型；

操作表:

• 创建表：

CREATE TABLE 表名(

  列名 列类型,

  列名 列类型,

  ......

);

CREATE TABLE  stu (

    id int(11) DEFAULT NULL,

    _name varchar(20) DEFAULT NULL,

    _age int(3) DEFAULT NULL,

    _class_no int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 查看当前数据库中所有表名称：SHOW TABLES；　
• 查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；
• 查看表结构：DESC emp，查看emp表结构；
• 删除表：DROP TABLE emp，删除emp表；
• 修改表：

1.  修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

2.   修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

3.  修改之允许值为空

ALTER TABLE stu MODIFY sid CHAR(6) NULL;

4. 修改之修改默认值

ALTER TABLE  stu ALTER COLUMN sid DROP DEFAULT;//删除默认值

ALTER TABLE  stu ALTER COLUMN sid SET DEFAULT 2;//设置默认值

或者ALTER TABLE stu CHANGE sid sid char(6) DEFAULT NULL;

3.  修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

4.  修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

mysql中alter语句中change和modify的区别

ALTER COLUMN：设置或删除列的默认值（操作速度非常快）

altertable film altercolumn rental_duration setdefault5;altertable film altercolumn rental_duration dropdefault;

CHANGE COLUMN：列的重命名、列类型的变更以及列位置的移动

ALTERTABLE MyTable CHANGECOLUMN foo bar VARCHAR(32) NOTNULLFIRST;ALTERTABLE MyTable CHANGECOLUMN foo bar VARCHAR(32) NOTNULLAFTER baz;

除了不能给列重命名之外，他干的活和CHANGE COLUMN是一样的

ALTERTABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOTNULLAFTER baz;

l  DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

插入数据

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

insert into stu (id,_age,_name,_class_no) values (1, 13, '张三', 2 );

insert into stu (id,_age,_name) values (1, 12, '李四' );

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值,并且数据类型以及列数必须匹配上.

修改数据

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

update stu  set _class_no = 1 where _class_no is null;

update stu set  id = 4 where _name = '张飞';

update stu set _class_no = 1;

删除数据

DELETE FROM 表名 [WHERE 条件]

delete from stu where _name = '张飞 ';

delete from stu;

TRUNCATE TABLE 表名

TRUNCATE TABLE stu;

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

l  DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

创建用户

CREATE USER 用户名@地址 IDENTIFIED BY '密码';

 create user kevin@localhost identified by '321';'

给用户授权

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP

grant all on for_test.* to kevin@localhost;

grant create, alter, drop, insert, update, select on for_test.* to kevin@localhost;

撤销授权

REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名@IP

 revoke create,alter,drop on for_test.* from kevin@localhost;

查看用户权限

SHOW GRANTS FOR 用户名@IP

show grants for kevin@localhost;

修改用户密码

UPDATE USER SET PASSWORD=PASSWORD('密码') WHERE User=’用户名’;

FLUSH PRIVILEGES;

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE USER='kevin'

FLUSH PRIVILEGES;

删除用户

DROP USER 用户名

DROP USER kevin;

l  DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

  SELECT selection_list /*要查询的列名称*/

  FROM table_list /*要查询的表名称*/

  WHERE condition /*行条件*/

  GROUP BY grouping_columns /*对结果分组*/

  HAVING condition /*分组后的行条件一般会跟聚集函数*/

  ORDER BY sorting_columns /*对结果排序*/

  LIMIT offset_start, row_count /*结果限定*/

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

• =、!=、<>、<、<=、>、>=；
• BETWEEN…AND；
• IN(set)；
• IS NULL；
• AND；
• OR；
• NOT;

其中<> 表示不等于,同!

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

查询姓名由5个字母构成的学生记录

select * from stu where sname like '_____';

其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

select * from stu where sname like '____i ';

查询姓名以“z”开头的学生记录

select * from stu where sname like 'z%';

其中“%”匹配0~n个任意字母。

查询姓名中第2个字母为“i”的学生记录

select * from stu where sname like '_i%';

查询姓名中包含“a”字母的学生记录

select * from stu where sname like '%a %';

去除重复记录

select distinct sal from emp;

查看雇员的月薪与佣金之和

select sal + mgr from emp;

+号两侧列字面值为数值类型就可以做加和(若非数值列会自动转换), 若其中一列为null则最终运算结果为null;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

select sal + IFNULL(comm,0) from emp;

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0) total FROM emp;

排序

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

聚合函数

聚合函数是用来做纵向运算的函数：

• COUNT()：统计指定列不为NULL的记录行数；
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算,认为NULL为0;
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算,认为NULL为0;
• SUM()：计算指定列的数值和，如果指定列字面值不是数值，那么计算结果为0,认为NULL为0;
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0,认为NULL为0;

查询有佣金的人数，以及有领导的人数：

select count(comm), count(mgr) from emp;

分组

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组

where与having

两者都用于设置限定条件,

区别是,where作用于筛选记录, having作用于筛选分组, where后不能跟聚集函数,而having可以, 

总之,WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句. HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句.

LIMIT（MySQL方言）

LIMIT用来限定查询结果的起始行，以及总行数。

注意，起始行从0开始，即第一行开始！

分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

    - 第一页记录起始行为0，一共查询10行；

    - 第二页记录起始行为10，一共查询10行；

    - 第三页记录起始行为20，一共查询10行；

完整性约束

完整性约束是为了表的数据的正确性！如果数据不正确，那么一开始就不能添加到表中。

主键唯一约束

    - 创建表：定义列时指定主键

create table stu(sid char(2) primary key,sname varchar(20),age int, gender varchar(10));

    - 创建表：定义列之后独立指定主键：

create tab le stu(sid char(6),sname varchar(20),age int, gender varchar(10),primary key(sid));

    - 修改表时指定主键：

alter table stu add primary key(sid);

删除主键（只是删除主键约束，而不会删除主键列）：

alter table stu drop primary key;

主键自增长

• 创建表时设置主键自增长(主键必须是整型才可以自增长)：

create table stu (sid int primary key auto_increment,sname varchar(20),age int, gender varchar(10));

• 修改表时设置主键自增长：

alter table stu change sid sid int auto_increment;

• 修改表时删除主键自增长：

alter table stu change sid sid int;

非空约束

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

• 创建表时设置非空约束

create table stu(sid char(2) primary key,sname varchar(20) not null,age int, gender varchar(10));

• 修改表时设置非空约束

alter table stu modify column sname varchar(20) not null;

• 删除非空约束

alter table stu modify column sname varchar(20) null;

唯一

为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！

NIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

-建表时设定唯一约束

create table stu (sid int(11), sname varchar(20) unique,sage int(11),gender varchar(10));

create table stu (sid int(11), sname varchar(20),sage int(11),gender varchar(10),unique(sname));

-建表后天就唯一约束

alter table stu add unique(sname);

alter table stu add constraint uq_sid_sname unique(sid,same)

-删除唯一约束

alter table stu drop index uq_sid_sname;

外键

主外键是构成表与表关联的唯一途径！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

create table t_user(uid int(11) primary key auto_increment,uname varchar(20));

create table t_section(sid int(11) primary key auto_increment, sname varchar (20),uid int(11),foreign key(uid)references t_user(uid));

表与表之间的关系

• 一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
• 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
• 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
• 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
• 多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

编码

查看mysql编码 show variables like 'char%';

因为当初安装时指定了字符集为UTF8，所以所有的编码都是UTF8。

• character_set_client：你发送的数据必须与client指定的编码一致！！！服务器会使用该编码来解读客户端发送过来的数据；
• character_set_connection：通常该编码与client一致！该编码不会导致乱码！当执行的是查询语句时，客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致，那么转换就不会出现问题；
• character_set_database：数据库默认编码，在创建数据库时，如果没有指定编码，那么默认使用database编码；
• character_set_server：MySQL服务器默认编码；
• character_set_result：响应的编码，即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码；

多表查询

多表查询就是一次查询涉及到多张表！多表查询分为连接查询和子查询，我们这里简单介绍一下连接查询！

多表查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想让的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

内连接 左连接 右连接

内连接

简化语法: select * from emp e, dept d where e.deptno = d.deptno;

正规语法(SQL99):select * from emp e inner join dept d on e.deptno = d.deptno;或者select * from emp e inner join dept d using (deptno); 

只有关联字段名称相同时才可以使用这种方式，而且这么方法关联字段只会出现一次，而上面的会出现两次（emp和dept表都有deptno字段）。

左连接: select * from emp e left join dept d on e.deptno = d.deptno;

因为emp是左表，所以左表中的记录无论是否满足条件都会出现。左表中不满足条件的行，右表部分使用NULL补空；

外连接:select *  from emp e right join dept d on e.deptno=d.deptno：因为dept是右表，所以右表中的记录无论是否满足了条件都会陋。右表中不满足条件的行，左表部分使用NULL补空。

数据库备份与恢复

备份:mysqldump -u用户名 -p密码 数据库名>生成脚本文件的路径

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

恢复:source 文件路径; 或者mysql -u用户名 -p密码 数据库<要执行的脚本文件路径

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！

执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！