数据库
SQL(Structure Query Language)语言是数据库的核心语言。
SQL语言共分为四大类：数据查询语言DQL，数据操做语言DML，数据定义语言DDL，数据控制语言DCL。
1.DDL数据库定义语言
1.1创建数据库
create database 库名；
create database if not exists 库名；
-- 方式1create database mydb_01;-- 方式2create database if not exists mydb_01;
1.2查看创建数据库的默认字符集
show create database 库名；
show create database mydb_01;
1.3修改数据库的字符集
alter database 库名 (default)可以省略 character set=字符集;
alter database mydb_01 default character set gbk ;
1.4删除库
drop database 库名;
drop database if exists 库名;
-- 方式1drop database mydb_02;-- 方式2drop database if exists mydb_03;
1.5显示库
show databases;
1.6 模糊查询mysql服务中所有的带character字符集的全局变量
show variables like ‘%character%’ ;
1.7创建表
注意事项:在给库中创建表的时候，首先要指明是给哪个库建表
use 库名;
create table 表名(
列名1 字段类型1,
列名2 字段类型2,
…
列名n 字段类型n
) ;
mysql> create table student(-> id int,-> name varchar(20),-> gender varchar(10),-> address varchar(50),-> age int-> )-> ;
数据库常用的字符类型
int------>默认int(11) 取的是当前实际长度 (推荐)
varchar:字符串类型数据 ‘’ “” 不写 引号 ,习惯去使用’'或者双引号
double(值1,值2):小数类型 ；double(3,2) ----小数类型,3位数,小数点后保留2位 ；123.56
日期类型：
date 仅仅表示日期,不表示时分秒；“-8-12”
datatime 不仅表示日期具体的时间；“-8-12 17:31:00”
timestap:时间戳 给表中插入数据, 显示当前插入/修改/删除/查询数据那一刻时间 (具体到秒"-8-12 18:00:01")
1.8查询数据库中有哪些表
show tables ;
1.9查询当前表的结构
desc 表名 ;
1.10修改表
给student表添加一列
alter table 表名 add 字段名称 字段类型;
alter table student add email varchar(50) ;desc student;
修改表中的字段类型
alter table 表名 modify 字段名称 更改后的字段类型;
alter table student modify address varchar(30) ;desc student;
修改表中的字段名称
alter table 表名 change 旧列名 新列名 字段类型;
alter table student change gender sex varchar(10) ;desc student;
修改表:删除某一列字段
alter table 表名 drop 字段名称;
alter table student drop email;desc student;
修改表的表名
alter table 表名 rename to 新表名;
alter table student rename to stu;show tables;
复制一张表结构和以前表的结果一样
create table 新表名 like 旧表名;
create table teacher like student;show tables;
1.11删除表
drop tabble 表名;
drop table if exists 表名;
-- 方式1drop table teacher;-- 方式2drop table if exists teacher;
2.DML操作数据库语言
2.1插入语句
一次插入一条记录：insert into 表名 values(值1,值2,值3…) ;插入全表数据 :那么每一个参数值必须和字段类型匹配!
**一次插入多条记录：insert into 表名 values(值1,值2…),(值1,值2…),(值1,值2…) ; **
插入部分字段：insert into 表名(部分字段1,字段2…) values(值1,值2…)注意:插入部分字段,必须字段数量和插入值的数量必须匹配，不管插入部分字段还全部字段,那么必须要和当前类型一致
-- 一次插入一条insert into stu values(1,'高圆圆','女','西安市',30) ;-- 一次插入多条INSERT INTO stu VALUES (3,'文章','男','西安市',35),(4,'马伊琍','女','上海市',42),(5,'德玛西亚','男','艾欧尼亚',25) ;-- 插入部分字段INSERT INTO stu(NAME,sex,address) VALUES('王宝强','男','咸阳市') ;
2.2修改表的数据
一次修改一个字段：按条件修改 update 表名 set 字段名称 = 值 where 字段名称= 值; 修改一个字段
一次修改对各字段：update 表名 set 字段名称1 =值1 ,字段名称2 =值2 where 条件;
如果不带条件修改:就是批量修改
-- 一次修改一个字段UPDATE stu SET id = 7 WHERE NAME = '王宝强' ;UPDATE stu SET age = 37 WHERE id = 7 ; -- 一次修改多个字段UPDATE stuSET NAME = '张三丰',sex = '男'WHEREid = 6 ;-- 批量修改UPDATE stu SET NAME = '高圆圆' ; -- 一般都是带条件进行修改!
2.3删除表的数据
带条件的删除
delete from 表名 where 字段名称 = 值; //删除指定的记录
删除全表数据
delete from 表名;
truncate table 表名;
-- 删除id = 6的这条记录DELETE FROM stu WHERE id = 6 ;-- 删除全表数据DELETE FROM stu ;TRUNCATE TABLE stu ;/*delete from 表名和truncate table 表名; 二者删除全表的区别1)delete from 表名:只是删除全表数据;表的结构还存在,如果表中 存在主键并且自增长约束,那么不会受影响,下一次在插入数据继续在之前的基础上继续自增长!2)truncate table 表名 ; 将表中数据删除的同时删除了表,然后在创建一张一模一样空表,肯定影响自增长主键的值,再次插入数据,自增长从1开始...等价于drop table my_use;直接删除表然后创建一个当前一模一样的表结构*/
2.4最基本的查询语句
查询全表数据
select * from 表名;（实际开发中,* (通配符),一般查询全表数据,需要带全部字段）
查询指定的字段
select 字段名称列表 from 表名;
-- 查询全表数据-- select * from 表名;SELECT * FROM stu ; -- 查询全表数据，指定全部字段SELECT id ,NAME,sex,address,ageFROM stu ;-- 查询指定的字段-- select 字段名称列表 from 表名;SELECT id ,NAME ,address,ageFROM stu ;-- 查询指定字段时可以通过as 关键字指定别名,as可以省略，‘’也可以省略SELECTid AS '学生编号',NAME AS '姓名',address AS '地址',age AS '年龄'FROM stu ;
3.DQL条件查询语句
3.1where条件查询
可以基本运算符:比较运算符(<,>,<=,>=,!=)/逻辑运算符(|| && /and /or)/赋值运算符 =
where后面多个or in(集合数据) 在两个值之间 between 值1 and 值2
mysql 判断某个字段为null , is null /is not null
-- 创建一个学生表CREATE TABLE student ( id INT, -- 编号NAME VARCHAR(20), -- 姓名age INT, -- 年龄 sex VARCHAR(5), -- 性别 address VARCHAR(100), -- 地址 math INT, -- 数学english INT -- 英语 );-- 显示结构DESC student ;-- 给学生表插入数据INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男',' 杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩 ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港 ',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);-- 条件查询 -- 查询年龄大于20岁的人的全部信息SELECT id '编号',NAME '姓名',age '年龄',sex '性别',address '地址',math '数学成绩',english '英语成绩'FROM student WHEREage > 20 ;-- 查询年龄在20岁和30岁之间的学生信息SELECT*FROMstudentWHEREage >= 20 && age <=30 ; -- &&:Java的逻辑运算符-- 另一种语法SELECT*FROMstudentWHEREage >= 20 AND age <=30 ;-- and mysql表示并列关系-- 另一种语法:mysql 在两个值之间 between 值1 and 值2SELECT -- 查询部分字段 并且年龄在20-30NAME '姓名',age '年龄',address '地址',math '数学成绩',english '英语成绩'FROM student WHEREage BETWEEN 20 AND 30 ;-- 查询年龄是18或者20或者30岁的学生的编号,姓名,年龄以及地址 (|| 或者 or)SELECTid '编号',NAME '姓名',age '年龄',address '地址'FROM student WHERE age = 18 OR age = 20 OR age = 30 ;-- mysql另一种语法 where 字段名称 in(多个值)SELECTid '编号',NAME '姓名',age '年龄',address '地址'FROM student WHERE age IN(18,20,30) ;-- 查询英语成绩为null的学号编号,姓名,性别,地址.数学成绩信息/* selectid ,name,sex,address,mathfromstudentwhere english == null ; -- java中的用法可以这样用 == = (都不行)*/SELECTid ,NAME,sex,address,math,englishFROMstudentWHERE english IS NULL ; -- mysql用法-- 查询英语成绩不为null的人 ,is not null 的用法SELECTid ,NAME,sex,address,math,englishFROMstudentWHERE english IS NOT NULL ; -- 查询英语和数学成绩总分的学生SELECT id 编号 ,NAME 姓名 ,sex 性别 ,address'地址',-- (math+english) as '总分'(math+IFNULL(english,0)) '总分'FROM student ;-- mysql 内置函数ifnull(值1,值2) ; ifnull(english,0) :-- 如果当前英语成绩为null,给默认值0-- 查询地址SELECTaddress 地址FROM student ;-- 查询字段,对字段去重 (distinct)SELECT DISTINCT address 地址 FROM student ;-- 查询年龄不是20岁的学生信息SELECT*FROM studentWHERE age != 20 ; -- != Java中这种语法 !=-- mysql中的不等于 <> SELECT *FROM studentWHEREage <> 20 ;
3.2like模糊查询
-- 模糊查询mysql服务中带字符集相关的变量 show variables like '%character%' ; -- 模糊查询 like-- select 字段列表 from 表名 where 字段名称 like '%字符%' ;/*% :包含的指定的字符 使用'%字符值%' 模糊查询包含指定字符的信息_ :代表单个字符(一个_下划线代表一个字符)两个相结合使用: '_%字符值%_' 三个字符:中间字符包含指定的值进行模糊查询*/-- 查询当前学生表中姓名包含马的学生信息SELECT *FROM studentWHERENAME LIKE '%马%' ;-- 查询第二个字符包含化的学生信息SELECT*FROMstudentWHERE NAMELIKE'_%化%' ;-- 查询姓名是三个字符的人SELECT *FROM studentWHERE NAMELIKE'___' ;-- 应用场景: 搜索框中输入关键字查询---使用到模糊查询
3.3聚合函数 count(列名)/max(列名)/min(列名)/sum(列名)/avg(列名)
-- 聚合函数查询---- >查询结果:单行单列的数据-- count(列名) :总记录数-- max(列名): 最大值-- min(列名字段):最小值-- sum(字段名称):求和-- avg(列名):平均分-- select 聚合函数(列名) from 表名;-- 查询当前student这个表的全部总记录数 -- 如果使用english字段查询总记录数SELECT COUNT(english) -- 使用业务字段查询(可能某个值为null,不会进行记录)FROM student ; -- 7条记录-- 可以使用函数设置如果当前某个值null,给默认值SELECTCOUNT(IFNULL(english,0)) 总记录数FROMstudent;/*count(列名)查询总记录数的时候,一般都使用非业务字段查询studentid(非业务字段)name age gender (业务字段)学校里面的学生表某个学生刚开始还在学习, 后面转学了 (非业务字段id---主键+自增长的)1张三 20 男selectcount(id)from 表名;*/-- 建议id来查询SELECTCOUNT(id) 总条数FROM student ;-- 查询数学的平均成绩 avg(列名)SELECTAVG(math) '数学平均成绩'FROMstudent ;-- 79.5000-- 查询英语成绩---总成绩(求和 sum(列名))SELECTSUM(IFNULL(english,0)) 英语总分FROMstudent ;-- 查询英语成绩的最高成绩SELECTMAX(IFNULL(english,0)) 英语最高分FROM student ;-- 查询数学成绩最低分SELECTMIN(math) 数学最低分FROMstudent ;-- 聚合函数使用最多的:count函数,avg函数
3.4排序查询 order by
-- 排序查询:order by 字段名称 asc/desc (升序/降序)-- select 字段列表 from 表名 order by 字段名 排序规则; -- 单个字段进排序-- 数学成绩安装升序排序SELECT*FROMstudent ORDER BY math ; -- 如果字段名称后面没有带排序规则:则默认升序排序-- 英语降序排序SELECT NAME ,age,sex,address,IFNULL(english,0) 英语成绩FROM studentORDER BY english DESC ; -- 降序-- 针对多个字段同时排序,当前第一字段值相等,则按照第二个字段的排序规则执行-- select 字段列表 from 表名 order by 字段名称1 升序1 ,字段名称2 升序2;-- 查询全表数据,数学成绩降序,英语成绩升序SELECT*FROMstudent ORDER BY math DESC,english ASC ;
3.5分组查询 group by
-- 分组查询:group by -- 分组group by 分组字段;-- 查询的时候可以查询分组字段, -- group by 后面不能使用聚合函数-- 问题:如果group by 和where条件语句一块使用,先后顺序? where之后 才能使用group by-- 现在需要按照性别分组-----分组之后查询出总人数 -- 性别-- 男/女 SELECT-- 查询分组字段sex '性别',COUNT(id) '人数'FROMstudentGROUP BY sex ; -- 性别-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分-- 条件:数学成绩不大于70分的人不参与分组;/*SELECT-- 查询分组字段sex '性别',COUNT(id) '人数'FROMstudentGROUP BY sex -- 性别where math > 70 ;*/SELECTsex '性别', -- 查询分组字段COUNT(id) '总人数',AVG(math) '数学平均成绩'FROMstudentWHERE math > 70 -- 条件:数学成绩大于70分人参与分组GROUP BY sex ; -- 性别分组
3.6筛选查询 having
-- 筛选 having-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分-- 条件:数学成绩不大于70分的人不参与分组 -- 筛选条件:总人数大于2的一组-- having 必须置于group by 之后,where 置于 group by 之前-- group by不能聚合函数,但是having后面可以聚合函数SELECTsex '性别', -- 查询分组字段COUNT(id) '总人数',AVG(math) '数学平均成绩'FROMstudentWHERE math > 70 -- 条件:数学成绩大于70分人参与分组GROUP BY sex -- 性别分组HAVING COUNT(id) > 2 ;-- 优化SELECTsex 性别, -- 查询分组字段COUNT(id) 总人数,AVG(math) 数学平均成绩FROMstudentWHERE math > 70 -- 条件:数学成绩大于70分人参与分组GROUP BY sex -- 性别分组HAVING 总人数 > 2 ;
3.7分页查询limit
-- 分页查询limit-- select 字段列表 from 表名 limit 起始行数,每页显示多少条;-- 给student表在插入三条记录INSERT INTO student VALUES(9,'德邦',19,'男','西安',78,58) ,(10,'vn',20,'女','宝鸡',89,65),(11,'亚索',22,'男','西安',95,74);-- 每页显示3条记录-- 查询第一页的数据-- limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;SELECT * FROM student LIMIT 0,3 ;-- 查询第二页的数据SELECT * FROM student LIMIT 3 ,3 ;-- 查询第三页数据SELECT * FROM student LIMIT 6,3 ;-- 第四页数据SELECT * FROM student LIMIT 9,3 ;-- 查询全表SELECT * FROM student ;UPDATE student SET english = 98 WHERE id = 7 ;
3.8数据库约束
1）默认约束,当前没有给那个字段设置值的时候,此时默认约束就会起作用（当某个值为空的时候默认使用的值）
2）非空约束
3）唯一约束 nuique
4）主键约束 (非空+唯一特点) primary key
-- 1）默认约束CREATE TABLE test(id INT , -- 编号NAME VARCHAR(10) , -- 姓名gender VARCHAR(2) DEFAULT '女' -- 性别 -- 默认约束 防止出现非法数据null(没有插入造成null值)) ;-- 通过sql语句修改表的类型,删除默认约束ALTER TABLE test MODIFY gender VARCHAR(2) ;-- 修改表加入默认约束ALTER TABLE test MODIFY gender VARCHAR(2) DEFAULT '女' ;-- 2）非空约束DROP TABLE test ;CREATE TABLE test(id INT ,NAME VARCHAR(10) NOT NULL -- 非空约束);-- 删除非空约束ALTER TABLE test MODIFY NAME VARCHAR(10) ;-- 修改表,加入非空约束ALTER TABLE test MODIFY NAME VARCHAR(10) NOT NULL ;-- 3）唯一约束DROP TABLE test;CREATE TABLE test(id INT ,NAME VARCHAR(10),phone VARCHAR(11) UNIQUE -- 唯一约束 :可以有null值,不能重复) ;-- 通过语句删除唯一约束-- alter table test modify phone varchar(11) ; 错误语法-- 删除唯一约束的sql alter table test drop index 字段名称;ALTER TABLE test DROP INDEX phone ;-- 添加唯一约束ALTER TABLE test MODIFY phone VARCHAR(11) UNIQUE ;-- 4）主键约束 (非空+唯一特点) primary key -- 都会给当前非业务字段去设置主键(xxid)DROP TABLE test ;CREATE TABLE test(id INT PRIMARY KEY , -- 非业务字段NAME VARCHAR(10),gender VARCHAR(2)) ;INSERT INTO test VALUES(1,'洪学佳','男'),(2,'马三奇','男') ;-- insert into test values(1,'马嘉钰','男') ;id值重复 -- Duplicate entry '1' for key 'test.PRIMARY'-- insert into test values(null,'雷郁','男') ;-- id直接插入null值 Column 'id' cannot be null
