'''SQL语句主要分为: DQL^:数据查询语句,用于数据进行查询 DML^:数据操作语言,对数据进项增加,修改,删除,如insert,update,delete TPL:事务处理语言,对事物进行处理,包括begin transaction , commit , rollback DCL:数据控制语言,进行授权与权限回收,如 grant,revoke DDL:数据定义语言,进行数据库,表的管理等,如declare , drop CGL:指针控制语句,通过控制指针完成表的操作,如declare cursor ''' '''MySQL数据类型: 整型:int ,bit 小数:decimal 字符串:varchar ,char 日期时间:date, time, datetime 枚举类型:enum 特别说明: decimal 表示浮点数,如decimal(5,2)表示共存5位数,小数占两位 char 表示固定长度的字符串,如char(3),如果填充“ab”时会补一个空格位‘tab’ varchar 表示可变长度的字符串,如varchar(3),填充“ab”时就会存储‘ab’ 字符串text表示存储大文本,当字符大于4000时推荐使用 对于图片,音频,视频等文件,不存储在数据库中,而是上传到某个服务器,然后在表中存在该文件的地址 ''' '''命令语句: 链接数据库: mysql -u root -p mysql -u root -p123456 退出: exit/quit/ctrl+d 显示当前所有数据库: show databases; 显示时间: select now(); 显示数据库版本: select version(); 查看当前使用的数据库: select database(); 创建数据库: create database databasename; # 默认编码格式-拉丁 create database databasename charset=utf8; #确定编码格式 查看创建数据库的语句 show create database databasename; 删除数据库: drop database databasename; `` 相当于把文件确定位一个整体,而不是具备特殊含义 使用数据库: use databasename; 数据表的操作: 查看当前数据库中的表: show tables; 修改表名: alter table tablename rename to newtablename; 创建数据表: #create table tablename(id int, name varchar(30)); create table tablename(id int primary key not null auto_increment,name varchar(30) not null); 示例:创建学生表(学号,姓名,性别,年龄,生日,班级) ## 自增一定要设置主键,主键才能设置自增 ## create table student( id int(11) unsigned not null auto_increment primary key, name varchar(30), gender enum('男','女') default "男", age tinyint unsigned default 0, birthday date default '2000-01-01', cls_id int(11) unsigned ); 查看数据表的结构: show create table tablename; 插入数据: --插入一条数据 insert into tablename value(xxxx,xxxx); --插入多条数据: insert into tablename values (id,name,xx...),(xxx,xxx,xxx),...; 修改表-添加字段: alter table tablename add 列名 类型; 示例: alter table student add birthday datetime; 修改表-修改字段:重命名 alter table 表名 change 原名 新名 类型及约束; 示例: alter table student change birthday birth datetime; 修改表-修改字段 alter table 表名 modify 列名 类型及约束; 示例: alter table student modify birth date not null; 修改表-删除字段: alter table 表名 drop 列名; 示例: alter table student drop birth; 删除表: drop table tablename; 查看表的创建语句: show create table 表名; 使用 desc 查看表的结构: desc tablename; ''' # 数据的增删改查 练习 '''增删改查 --增加 --全列插入 -- insert [into] 表名 values() --向学生信息表student中插入信息 +----------+---------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+------------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | gender | enum('男','女') | YES | | 男 | | | age | tinyint(3) unsigned | YES | | 0 | | | birthday | date | YES | | 2000-01-01 | | | cls_id | int(11) unsigned | YES | | NULL | | +----------+---------------------+------+-----+------------+----------------+ insert into student value(0,'nike','男',18,'2000-01-01',1); insert into student value(null,'nike','男',18,'2000-01-01',1); insert into student value(default,'nike','男',18,'2000-01-01',1); --枚举中的下标从 1 开始 1-->男 2-->女 ... insert into student value(0,'nike',1,18,'2000-01-01',1) 注:输入的内容不在枚举之内,则报错 --部分插入 --插入一个 insert into student(name,gender) values('tony',1); --插入多行 insert into student values(id,name,age,gender,birthday), (id,name,age,gender,birthday),(id,name,age,gender,birthday); --修改 --update 表明 set 列1=值1,列2=值2....where 条件; update student set gender=1; # 全部都改 update student set gender=1 where id =2; update student set gender=1,name='amie' where id=2; --删除 --物理删除 --delete from 表名 where 条件 delete from student; delete from student where name='nike'; --逻辑删除 --给一个字段来表示,这条信息是否已经不在使用了 --alter table student add is_delete字段 bit 类型 --update student set is_delete=1 where id=6; --查询 --查询所有列 --select * from 表民; select * from student; --指定条件查询 select * from student where name='nike'; --查询 name 为 Nike的所有信息 select * from student where id>3; -- 查询id大于3的全部信息 --查询指定列 --select 列1,列2,....from 表名; select id,name from student; --字段的顺序 --可以使用as位列或表指定别名 --select 字段[as 别名] ,字段[as 别名] from 数据表 where ....; select name as '姓名' ,gender as '年龄' from student; ''' ''' # 创建数据库 create database python_test charset=utf-8; # 使用数据库 use python_test; # 显示当前数据是哪个: select database(); # 创建一个数据表: create table student(id int unsigned primary key not null auto_increment, name varchar(30) default '', age tinyint unsigned default 0, # height decimal(5,2), gender enum('男','女','中性','未知') default '未知', cla_id int unsigned default 0, is_delete bit default 0 ); create table classes( id int not null primary key auto_increment unsigned, name varchar(30) not null default '' ); # 插入数据: insert into student values (0,'张美',50,2,1,0), (0,'王红',36,2,1,0), (0,'王刚',40,1,1,0), (0,'钱张',28,3,1,0), (0,'阿凡',18,2,1,0), (0,'陈杰',22,1,1,0), (0,'陈宇航',22,1,1,0), (0,'洪兴',30,1,1,0); ''' ''' 查询 --查询所有列 --select * from 表民; select * from student; select id,name from student; --指定条件查询 select * from student where name='nike'; --查询 name 为 Nike的所有信息 select * from student where id>3; -- 查询id大于3的全部信息 --查询指定列 --select 列1,列2,....from 表名; select id,name from student; --select 表名.字段 ... from 表名; select student.name ,student.id from student; --可以使用as给字段指定别名 --select 字段[as 别名] ,字段[as 别名] from 数据表 where ....; select name as '姓名' ,gender as '年龄' from student; select id as '编号',name as '姓名' from student; --可以使用as给表起别名 select id ,name from student as s; 注:select student.id,student.name from student as s; 错误:起别名必须使用别名来指定查询的字段 注:select id ,name from student as '学生表'; 存在错误 错误:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''学生表'' at line 1 --消除重复段 distinct select distinct age from student; 条件查询 --比较运算符 --select .... from 表名 where ..... -- > select * from student where age>18; --< select * from student where age<18; -- >= select * from student where age>=18; --<= select * from student where age<=18; --= select * from student where age=18; --!= (python2中 <> 也是不等于) select * from student where age!=18; --逻辑运算符 --and --查询年龄在 18 到 28 之间的 select * from student where age>18 and age<28; # 错误用法 :select * from student where age>18 and <28; # 条件必须写全 -- 查询 18 岁以上的女性 select * from student where age >18 and gender =2; --or --查询男性或者中性的 select * from student where gender=1 or gender =3; --not -- 查询不在28岁以上的女性 select * from student where not (age>28 and gender=2); --查询年龄小于或者等于18并且式女性 select * from student where (not age<= 18) and gender =2; --模糊查询 -- like -- % 替换1个或者多个 -- _ 替换1个 -- 查询姓名以‘小’开始的名字 select * from student where name like '小_'; -- 查询有两个字的名字 select * from student where name like '__'; -- 查询有三个字的名字 select * from student where name like '___'; -- 查询至少有两个字的名字 select * from student where name like '__%'; -- rlike 正则表法式 -- 查询以 n 开头的名字 select * from student where name rlike '^小.*红$'; --范围查询 -- in (1,3,7) 表示在一个非连续的范围内 -- 查询年龄20 ,30 ,40, 50 的姓名 select name from student where age in (20,30,40,50); -- not in (1,2,3,4) 表示不在这个非连续的范围之内的 select name from student where age not in (20,30,40,50); -- between ... and ... 表示在一个连续的范围内 select * from student where age between 18 and 30; -- not between ... and ... 表示不在这个连续范围内的 select * from student where age not between 18 and 30; select * from student where not age between 18 and 30; --错误:select * from student where age not (between 18 and 30); --空判断 -- 判断 is null -- 查询删除标记信息是否为空 select * from student where age is null; --判断非空 is not null select * from student where is_delete is not null; --排序 -- order by 字段 -- asc 从小到大排序,及升序 -- desc 从大到小排序,及降序 -- 查询年龄在18到50岁之间的女性,id从大到小进行排序 select * from student where age between 18 and 50 and gender =2 order by id desc; --order by 多个字段 -- 查询年龄在18到50之间的人,性别从男到中性排,性别相同则根据id从大到小排 select * from student where age between 18 and 50 order by gender , id desc; --按照年龄从小到大ID从大到小进行排序 select * from student order by age ,id desc; -- 聚合查询 -- 总数 count -- 查询男性有多少人,女性有多少人,中性有多少人 select count(*) as '男性人数:' from student where gender=1; select count(*) as '女性人数:' from student where gender=2; select count(*) as '中性人数:' from student where gender=3; -- 最大值 max -- 查询最大年龄 select max(age) from student; -- 最小值 min -- 查询年龄最小 select min(age) from student; -- 求和 sum -- 求全部年龄总和 select sum(age) from student; --平均值 avg -- 计算平均年龄 select avg(age) from student; select sum(age)/count(*) from student; --四舍五入 round(123.12,2) 保留2位小数 -- 计算平均年龄,并且保留两位小数,小数存在误差,要是存储小数将小数扩大100或者1000倍进行储存 select round(sum(age)/count(*),2) from student; select round(avg(age),2) from student; -- 分组 -- group by --按照性别分组,查询所有性别 select gender from student group by gender; -- 计算每种性别的人数 select gender,count(*) from student group by gender; --计算男性的人数 select gender,count(*) from student where gender = 1 group by gender; --group_concat() -- 查询同种性别中的姓名 select gender,group_concat(name) from student group by gender; select gender,count(*),group_concat(name) from student group by gender; select gender ,count(*),group_concat(id,'-',name,'-',age) from student group by gender; select age from student group by age; select age,count(*),group_concat(name) from student group by age; --having 对分组进行判断 --查询平均年龄超过30岁的性别,以及姓名 having avg(age)>22 select gender ,group_concat(name) from student group by gender having avg(age)>22; select gender ,avg(age),group_concat(name) from student group by gender having avg(age)>22; select gender ,avg(age),count(*),group_concat(name) from student group by gender having avg(age)>22; -- 查询每种性别中人数多于5个的信息 select gender from student group by gender having count(gender)>5; select gender,group_concat(name),count(*) from student group by gender having count(gender)>5; select gender, count(*) from student group by gender ; where 和 having 的区别 1、where将单个行过滤到查询结果中,而having将分组过滤到查询结果中 2、having中使用的列名必须出现在group by列表中,或包括在聚集函数中。 -- 分页 -- limit ,start ,count -- limit 限制查询出来的个数 # 在 where ,order by ,group by中 ,limit 只能放在最后 select * from student limit 2; select * from student where gender=1 limit 2; -- limit 查询前五个 数据1 表示起始位置,数据2 表示显示个数 select * from student limit 0,5; -- limit 查询显示五个,第二页 select * from student limit 5,5; -- 查询所有女性信息,年龄从大到小只显示两个进行排序 select * from student where gender = 2 order by age desc limit 0,2; -- 连接查询 (连接多个表,取表中共同的东西) --内连接查询:查询的结果为两个表匹配的数据 --右连接查询:查询的结果为两个表匹配到的数据,对于左表中不存在的数据 --左连接查询:查询的结果为两个表匹配到的数据,左表有的数据,右表中不存在 -- select * from 表1 inner/left/right join 表2 on 表1.列 = 表2.列; ##行:row 列:column## -- select .... from table1 inner join table2 on table1.column_name = table2.column_name ; -- 查询又能够对应班级的学生以及班级信息 select * from student inner join classes on student.cls_id = classes.id; --按要求显示姓名 ,班级 select s.name ,c.name from student as s inner join classes as c on s.cls_id = c.id; --给表起别名 select s.name ,c.name from student as s inner join classes as c on s.cls_id = c.id; --查询有能够对应班级的学生以及班级信息,显示学生的的所有信息,只显示班级名称 select * , classes.name from student inner join classes on student.cls_id = classes.id; --在以上查询中,将班级姓名显示在第1列 select from student as s inner join classes as c on s.cls_id = c.id; --查询有能够对应班级的学生以及班级信息,按照班级进行排序 select c.name ,s.* from student as s inner join classes as c on s.cls_id = c.id order by c.id; --但同一个班级的时候,按照学生的id进行从小到大的排序 select c.name ,s.* from student as s inner join classes as c on s.cls_id = c.id order by c.id,s.id; --右连接 以右边的表为基准 -- left join .... on -- 查询美味学生对应的班级信息 select * from student left join classes on student.cls_id = classes.id; -- 查询没有对应班级信息的学生 -- 如果从原表中判断结果 用 where select ... from ... left join ... on ... where ... ; select * from student left join classes on student.cls_id = classes.id where classes.id is Null; -- 如果把查询出来的结果单座一个新的集 则用 having select ... from ... left join ... on ... having ... ; select * from student left join classes on student.cls_id = classes.id having classes.id is null; --左连接 以左边的表为基准 -- right join .... on -- 和右连接使用类似 -- 自联结 一个表中的内容和表中的id相关联 -- 查看省对应下的市 select a.name,p.name from areas as a inner join areas as p on a.aid = p.pid ; select a.name,p.name from areas as a inner join areas as p on a.aid = p.pid order by a.aid; --查看河南省下的市 select a.name ,p.name from areas as a inner join areas as p on a.aid = p.pid having a.name='河南省'; -- 子查询 -- 再查询里 放查询条件 select * from student where age = (select max(age) from student); '''