第六周预习：MySQL基础语法

MySQL

数据库操作

1.创建数据库
(1).character set：指定数据库采用的字符集，如果不指定字符集，默认时候是utf8
(2).collate：指定数据库字符集的校对规则 （常用utf8_bin、utf8_general_ci注意默认是utf8_general_ci)

create database mysql1;//默认创建数据库表mysql01
create database mysql1 default character set utf8;//创建指定默认字符集为交的数据库
show character set;//查看可用字符集名称
show collation;//查看可用的排序规则名称
create database student character set utf8 default collate utf8_bin;
//创建指定的默认字符集utf8，排序规则为utf8_bin的数据库（student）

2.查看、删除和修改数据库
(1).使用数据库：use 数据库名称;
(2).修改数据库：使用alter database语句可以修改一个已有的数据库的默认字符集和排序规则。
(3).删除数据库：drop database mysql1; ​ 每次只能删除一个数据库。

show databases;//查看所有数据库
show create databases;//查看数据库属性
use mysql01;//使用数据库
alter database mysql01 character set gbk;
//将指定的utf8的数据库修改为gbk的数据库
alter database student character set gb2312 default collate gb2312_chinese_ci;
//将student数据库的默认字符集修改为gb2312，排序规则修改为gb2312_chinese_ci
drop database mysql01;//删除数据库

表操作

1.创建表
create table shop（id int(10),goods_name varchar(10),price double(10));创建表
2.查看表
show tables;
3.查看表结构
desc 表名;
4.查看建表语句
show create table;
5.快速创建一个表结构相同的表
create table 新表名 like 旧表名;
6.删除表
drop table 表名;
7.删除表，如果表结构存在的话
drop table if exits 表名;

修改表结构

8.添加字段
alter table 表名 add [column] 字段名 类型;
9.删除字段
alter table 表名 drop 字段名;
10.修改字段名
alter table 表名 change 旧字段名 新字段名 类型;
11、修改字段类型
alter table 表名 modify 字段 新字段类型;
12.修改表名
rename table 表名 to 新表名;
13.修改表的字符集
alter table 表名 character set 字符集;

增删改查

insert(添加数据)

插入全部数据
insert into 表名 values (值1,值2);
值的顺序一定要与字段对应
insert into user values('张三','30','133324','男');
插入部分数据
insert into 表名 (字段名1，字段名2，字段名3,...)values(值1,值2,值3,...);
insert into user(name,age,modile) values('小艾',20,'243252');
insert into 表名(字段名1,字段名2,字段名3,...)values(值1,值2,值3,...),(值1,值2,值3,...)...;
复制表
目标表必须存在，并且表结构要与源表一致。
insert into 目标表名 select*from 源表名;
update(修改数据)
更新表记录
update 表名 set 字段名1 = 值1， 字段名2 = 值2，......
更新全部记录
update user1 set gender = '男';
更新时加入运算
update user1 set age = age+1;
部分更新：
update 表名 set 字段名1 = 值1，字段名2 = 值2，...where 字段条件;
update user1 set sex='女' where name = '张三';

delete(删除数据)

删除记录
delete from 表名; 删除全部记录
delete from 表名 where 字段条件; 按条件删除
delete from user1 where name = '张三';
delete from user1 where age<20;
delte from user1 where age<23 and name = '小天';
delete from user1; 删除表内全部数据
删除全部记录
truncate 表名;
truncate user1;
delete与truncate的区别
delete是将表中的数据逐行清除。如果表里的数据过多速度会很慢，但是它支持按条件删除
truncate是直接将表中的数据物理删除，删除速度快和表里有多少数据无关，它不支持条件删除

select(查找数据)

查询语句
select 关键字用来查询取出表中的数据
简单查询：
select *from 表名；取出表中的所有数据
查询指定列：
select 字段名1，字段名2，.... from 表名
查询指定列带别名：
select 字段名1 as 别名 ，字段名2 as 别名，.....

where 关键字 按条件查询

相关运算符： < 小于 = 等于 >大于 >=大于等于 <=小于等于 != 或 <>不等于
英语不等于77
select * from student where english !=77;
math和英语都大于60的
select *from student where math>60 and ehglish>60;
数学大于60 或者英语大于 80
select * from student where math>60 or english>80;

逻辑运算符： and (&&)与 并且 or(||)或 not(!)非
select * from student where math not in(66,76);

范围关键字 between and
between 值1 and 值2 取出来的结果集范围包含 值1 和 值2
select * from student where math between 56 and 66；
等同于
select *from student where math >= 56 and math <=66;

模糊查询关键字：
%通配符 代表任意长度的任意字符
like ‘马%’
select * from student where name like '马%';
select * from student where name like '%德';
select * from student where name like '%德%';
_通配符 代表一个字符长度的任意字符 用法与%一样，只不过只代表一个字符
select * from student where name like ‘马_’;
select * from student where name like ‘马__’;
select * from student where name like ‘_岩’;

排序关键字
order by [asc] [desc] asc 按升排序 desc 降序排序
单列排序
select * from student order by math; asc默认可以不写
select * from student order by math desc; desc 必须写上

组合排序
order by 字段1 [asc|desc], 字段2 [asc|desc]
select * from student order by math desc, english desc;

函数

字符串函数

合并字符串函数 concat(s1,s2,…)
返回字符串长度 char_length(str);
返回字节长度 length(str);
将小写字母转为大写字母 ucase(str) 或 upper(str)
将大写字母转为小写 lcase(str) 或 lower(str)
去空格 trim(str) ltrim() rtrim()
字符替换 replace(s,s1,s2); s要操作的字符串，s1要替换的字符串，s2要替换成的字符串
字符串截取 substr(s,strat,length); s要操作的字符串，start开始位置，length长度
字符串比较 strcmp(str1,str2); str1大返回1 str1小返回-1

日期时间函数

now() 或curdate() curtime()
now()返回日期和时间
curdate()返回年月日
curtime()返回时分秒
year(now()或字段名) month(now()或字段名) day(now()或字段名)
last_day(now()或字段名) 返回一个月中的最后一天
adddate(日期,天数) 返回当前日期+天数后的日期
subdate(日期,天数) 返回当前日期-天数后的日期
quarter(日期) 返回日期为一年中的第几个季度
datediff(日期1,日期2) 返回两个日期之间差的天数 (注意日期一定要用引号引起来)
date_format(日期,’%Y-%m-%d’) 按指定格式显示日期。

数字相关的函数

ABS(X) 求绝对值
ceil(x) 向上取整
floor(x)向下取整
mod(x,y) 取余数
RAND() 返回0到1之间的随机数。
Round(任意小数) 四舍五入
truncate(x,y) 返回数值x 保留到小数点后y位的值。

聚合函数

count 统计数量
count(*) 统计共有多少行
​count(字段名)
select count(*) as 行数 from student;
min(字段名) 函数：
​ 求字段的最小值。
select min(math) from student;
max(字段名) 函数
​ 求字段的最大值。
select max(math) from student;
avg 求某列的平均值
select avg(ifnull(english,0)) as 平均分 from student;
–因为NULL不计入计算，所必须给null一个0值
select avg(math) from student; --没有空的列可以直接进行计算。
sum(字段名) 求合
select sum(english) from student;
select sum(ifnull(english,0)) from student;

分组

group by 将数据进行 分组
select sum(sex) from student group by sex;
按性别进行分组计算。
执行顺序是先进行分组，然后再按组对字段进行计算

按条件进行筛选
having关键字
select math,count() from student group by math having count()>1;
执行顺序：
where 在group by 之前执行
having 在group by 之后执行
先分组，在count()统计，再按条件显示

嵌套查询

select 语句可以把语句查询出来的结果集当成表来再次查询
select name from (select * from user_db where sex = ‘女’)as nigu;

组合查询

union 关键字
将两个结果集上下拼接起来。要求两个结果集的表结构一致
select *from student where sex ='女' union select * from student where name = '马化腾';
union 每个查询必须包含相同的列，表达式或聚集函数。
列数据类型必须兼容。
union包含自动去重功能

表连接

内连接：
内连接，查询两张表的交集数据
​ 1.隐式内连接
select a.name,b.spname from user6 as a,sp as b where a.id=b.userid;
​ 2.显示内连接
select * from sp as a inner join user6 as b on a.userid=b.id;
外连接
​ 1.左外连接
select 字段名1,字段名2，... from 表1 left [outer] join 表2 on 条件
​ 2.右外连接
select 字段名1,字段名2，... from 表1 right [outer] join 表2 on 条件