SQL语句
1.数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[,create_specification] ...]
create_specification: 7
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
CHARACTER SET:指定数据库采用的字符集
COLLATE:指定数据库字符集的比较方式
(查看mysql存储位置 :show global variables like “%datadir%”;)
练习:
- 创建一个名称为mydb1的数据库。
create database mydb1; - 创建一个使用utf8字符集的mydb2数据库。
create database mydb2 character set gbk;
创建一个使用utf8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin
2.查看、删除数据库
- 显示数据库语句:
SHOW DATABASES
- 显示数据库创建语句:
SHOW CREATE DATABASE db_name
- 数据库删除语句:
DROP DATABASE [IF EXISTS] db_name
- 练习:
1.查看当前数据库服务器中的所有数据库
show databases;
2.查看前面创建的mydb2数据库的定义信息
show create database mydb2;
3.删除前面创建的mydb1数据库
drop database mydb1;
3.修改数据库
ALTER DATABASE [IF NOT EXISTS] db_name
[alter_specification [, alter_specification] ...] alter_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
4.选择数据库
use db_name;
查看当前使用的数据库:
select database();
- 练习
查看服务器中的数据库,并把其中某一个库的字符集修改为utf8;
alter database mydb3 character set gbk;
5.数据库表
在选中一个数据库之后,可以在当前数据库中创建表。
1. 创建表(基本语句)
CREATE TABLE table_name(
field1 datatype,
field2 datatype,
field3 datatype
);[character set 字符集][collate 校对规则]
character set | 字符集 |
---|---|
collate | 校对规则 |
field: | 指定列名 |
datatype: | 指定列类型 |
注意:创建表时,要根据需保存的数据创建相应的列,并根据数据的类型定义相应的列类型。例:user对象
id | int |
---|---|
name | String |
password | String |
birthday | Date |
2.MySQL常用数据类型
- 字符串型
VARCHAR、CHAR 0~255
char(20) | 占据存储的空间一定。由于存储的数据长度是一定的,在读取数据时,也会读取固定的长度,无需判断数据长度,所以读取速度较快。 |
---|---|
varchar(20) | 占据存储的空间是变化的。 存储数据的长度是变化的,所以需要先判断数据长度,再去读取,相对char来说,varchar读取效率稍低。 |
如果优先考虑读取速度的话,可以将字段设置为char类型。如果优先考虑存储空间,可以将字段设置为varchar类型
- 大数据类型
BLOB、TEXT - 数值型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE - 逻辑型
BIT 1/0 - 日期型
DATE、TIME、DATETIME、TIMESTAMP
3.创建表练习
创建一个员工表employee ---- 查看表结构: desc 表名;
字段 | 属性 |
---|---|
id | 整型 int |
name | 字符型 varchar(10) |
gender | 字符型 char(2) |
birthday | 日期型 date |
entry_date | 日期型 date |
job | 字符型 varchar(20) |
salary | 小数型 double |
resume | 大文本型 text |
*创建一个员工表employee
create table employee(
id int primary key auto_increment ,
name varchar(20),
gender varchar(2) ,
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);
创建完毕之后利用desc employee 来查看表结构。
4.定义单表字段的约束
-
定义主键约束
primary key:不允许为空,不允许重复
删除主键:alter table tablename drop primary key ;
主键自动增长 :auto_increment
a. 一个任意类型的字段都可以设置为主键字段。
b. 如果这个字段为auto_increment,则这个字段一定是主键字段。
c. 设置主键的意义:作为索引使用,提高查询效率。 -
定义唯一约束
unique
例如:name varchar(20) unique -
定义非空约束
not null
例如:salary double not null -
外键约束
5.查看表信息
-
查看表结构:
desc tabName
-
查看当前所有表:
show tables
-
查看当前数据库表建表语句
show create table tabName;
6.修改表
- 使用 ALTER TABLE 语句追加, 修改, 或删除列的语法
ALTER TABLE table ADD column_name datatype [DEFAULT expr] [, column datatype]..;
ALTER TABLE table MODIFY column_name datatype [DEFAULT expr] [, column datatype]...;
ALTER TABLE table DROP column_name;
- 修改表的名称:
rename table 表名 to 新表名;
- 修改列的名称:
ALTER TABLE table_name change old_column new_column typefiled;
- 修改表的字符集:
alter table user character set utf8;
7.删除表
drop table tabName;
表数据操作–增删改查
1.数据库表记录CRUD语句
Insert语句 (增加数据)
Update语句 (更新数据)
Delete语句 (删除数据)
Select语句 (查找数据)
Insert语句
2.使用 INSERT 语句向表中插入数据。
INSERT INTO table_name [(column [, column...])] VALUES (value [, value...]);
- 插入的数据应与字段的数据类型相同。 数据的大小应在列的规定范围内,
例如:不能将一个长度为80的字符串加入到长度为40的列中。 - 在values中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 插入空值:不指定或insert
- into table value(null) 如果要插入所有字段可以省写列列表,直接按表中字段顺序写值列表
Insert语句练习
- 练习:使用insert语句向employee表中插入三个员工的信息。
字段 | 属性 |
---|---|
id | 整型 int |
name | 字符型 varchar(10) |
gender | 字符型 char(2) |
birthday | 日期型 date |
entry_date | 日期型 date |
job | 字符型 varchar(20) |
salary | 小数型 double |
resume | 大文本型 text |
- 重要 Tip:mysql中文乱码(mysql5.5)
- mysql有六处使用了字符集,分别为:client 、connection、database、results、server 、system。
- client是客户端使用的字符集。
- connection是连接数据库的字符集设置类型,如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
- database是数据库服务器中某个库使用的字符集设定,如果建库时没有指明,将使用服务器安装时指定的字符集设置。
- results是数据库给客户端返回时使用的字符集设定,如果没有指明,使用服务器默认的字符集。
- server是服务器安装时指定的默认字符集设定。 system是数据库系统使用的字符集设定。(utf-8不可修改)
show variables like’character%’;
set names gbk;临时修改当前CMD窗口和mysql的通信编码字符集 - 通过修改my.ini 修改字符集编码 请到mysql安装目录下面找到 my.ini文件
修改default-character-set=utf8 为 default-character-set=gbk
有两个地方都要改
修改文件前,先停止mysql服务 ,等修改后再重新启动
使用dos命令 :net stop mysql 来停止服务 net start mysql 来启动
3.Update语句
使用 update语句修改表中数据。
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。
Update语句练习
4.Delete语句
使用 delete语句删除表中数据。
delete from tbl_name [WHERE where_definition]
- 如果不使用where子句,将删除表中所有数据。
- Delete语句不能删除某一列的值(可使用update)
update table_name set 字段名=’’; - 使用delete语句仅删除记录,不删除表本身。如要删除表,使用drop table语句。
drop table table_name; - 同insert和update一样,从一个表中删除记录将引起其它表的参照完整性问题,在修改数据库数据时,头脑中应该始终不要忘记这个潜在的问题。
外键约束 - 删除表中数据也可使用TRUNCATE TABLE 语句,它和delete有所不同,参看mysql文档。
5.Select语句(1)
基本select语句
SELECT [DISTINCT] *|{column1, column2. column3..}FROM table;
select 指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。
from指定查询哪张表。
DISTINCT可选,指显示结果时,是否剔除重复数据
6.Select语句(2)
- 在select语句中可使用表达式对查询的列进行运算
SELECT * |{column1|expression, column2|expression,..}
FROM table;
-
在select语句中可使用as语句
SELECT column as 别名 from 表名;
7.Select语句(3)
使用where子句,进行过滤查询。练习:
查询姓名为XXX的学生成绩
查询英语成绩大于90分的同学
查询总分大于200分的所有同学
8.Select语句(4)
在where子句中经常使用的运算符
比较运算符 | > < <= >= = <> | 大于、小于、大于(小于)等于、不等于 |
---|---|---|
between …and… | 显示在某一区间的值 | |
in(set) | 显示在in列表中的值,例:in(100,200) | |
like ‘张pattern’ | 模糊查询%_ | |
is null | 判断是否为空 select * from user where id is null | |
ifnull(原值,替代值) | 如果原值为null,则使用代替值 select ifnull(score,0) from exam; | |
逻辑运算符 | and | 多个条件同时成立 |
or | 多个条件任一成立 | |
not | 不成立,例:where not(salary>100); |
Like语句中,% 代表零个或多个任意字符,_ 代表一个字符,例first_name like ‘_a%’;
9.Select语句(5)
使用order by 子句排序查询结果。
SELECT column1, column2. column3..
FROM table
order by column asc|desc
Order by 指定排序的列,排序的列既可是表中的列名,也可以是select 语句后指定的列名。
Asc 升序(默认)、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。
10.聚集函数-count
count(列名)返回某一列,行的总数
Select count(*)|count(列名) from tablename [WHERE where_definition]
11.聚集函数-SUM
Sum函数返回满足where条件的行的和
Select sum(列名){,sum(列名)…} from tablename[WHERE where_definition]
注意:
sum仅对数值起作用,否则会报错。
对多列求和,“,”号不能少。
12.聚集函数-AVG
AVG函数返回满足where条件的一列的平均值
Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]
13.聚集函数-MAX/MIN
Max/min函数返回满足where条件的一列的最大/最小值
Select max(列名)from tablename [WHERE where_definition]
14.Select语句(6)分组操作
使用group by 子句对列进行分组
SELECT column1, column2. column3..
FROM table
group by column having ...
- 使用having 子句 对分组结果进行过滤
- where和having区别:where在分组前进行条件过滤,having在分组后进行条件过滤。使用where的地方都可以用having替换。但是having可以使用分组函数,而where后不可以使用。
数据库的备份与恢复
1.备份数据库表中的数据
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
mysqldump -uroot -p db_name > d:/1.sql
2.恢复数据库
(注意:如果数据库已经删除,先创建数据库再恢复数据。)
- 方式一: 在cmd中:
mysql –u 用户名 -p 数据库名 < 文件名.sql
mysql -uroot -p db_name < d:/1.sql
mysql -uroot -p mydb3 < d:/1.sql
- 方式二: 在mysql客户端中
source 文件名.sql
source d:/1.sql
数据库表间关系
1.表间对应关系
-
1 对1
-
1对多
-
多对多
注意:虽然建立的表与表之间的关系,但是这个关系并不会被数据库维护.需要通过外键约束来通知数据库帮助我们维护表间关系.
外键约束
1.外键约束
可以使用外键约束来通知数据库维护表与表之间数据的完整性和正确性.
外键的作用:
维护数据的完整性和正确性.
foreign key(ordersid) references orders(id)
2.案例:
新建部门表dept(id,name)
通过外键约束建立与员工表emp关系
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);
-
拓展:
外键不推荐使用。
在高吞吐的情景下,应该优先保证数据入库,如果使用外键,则可能会发生数据无法正确入库的情况,造成数据丢失。所以不推荐使用外键。
多表查询
1.多表查询操作
select * from dept,emp;
上述操作为笛卡尔积查询。
a.笛卡尔积查询:
是将左边表的数据数量(m)乘以右边表的数据数量(n)。
总数据量=m*n
在笛卡尔积查询的基础之上,添加一个判断条件:
dept.id = emp.dept_id
select * from dept,emp where dept.id = emp.dept_id;
b.内连接查询
在笛卡尔积查询的基础之上,获取左边表有且右边表也有的数据。
select * from dept
inner join emp
on dept.id =emp.dept_id;
c.外连接查询
- 左外连接查询
在内连接查询的基础之上,获取左边表有但右边表没有的数据。
select * from dept
left join emp
on dept.id = emp.dept_id;
- 右外连接查询
在内连接查询的基础之上,获取右边表有但左边表没有的数据。
select * from dept
right join emp
on dept.id = emp.dept_id;
注意:
别名
select * from dept d
right join emp e
on d.id = e.dept_id;
获取表中指定字段
select d.id,d.name,e.name from dept d
right join emp e
on d.id = e.dept_id;
- 全外连接查询
在内连接的基础之上,获取左边表有且右边表没有的数据和右边表有且左边表没有的数据。
mysql中没有全外连接关键字。 full join
union 联合–将两个查询结果合并,并去除重复内容。
select * from dept left join emp on dept.id = emp.dept_id
union
select * from dept right join emp on dept.id = emp.dept_id;