mysql代码大全

mysql 基础片

代码如下：

1.创建语句(create)

​ 创建数据库:两种方法

​ 1.直接鼠标

​ 2.使用mysql命令

​ eg:create database 数据库名；(左上角的信件查询——》输入命令)

2.创建表(table)

		create table 表名 (字段1 类型 [约束] [注释],字段2 类型  [约束] [注释] ........ );

​ 注意sql命令最后一个条件的最后一个不用逗号,不然会出错

eg:CREATE TABLE 神(
学号 int(10) PRIMARY key,
姓名 varchar(5) not null,
年龄 int(2) check(年龄>=18and年龄<=2),
数学成绩 float,
语文成绩 float

);

3.给指定列添加数据

​ 格式:insert into 表名(列1(学号),列2(姓名)…) value (值1(学号),值2(姓名));

​ eg:insert into 学生(姓名) value(‘阿玛尼’)

4.给一个表全部列增加数据

​ 格式: insert into 表名 values (值1(姓名),值2(性别))

​ 注意:全部添加数据要对照属性全部并且给予数据，可以这样理解:你的表中有 “姓名” “学号” “语文” ‘‘数学’’，然后给他们全部值 比如 : “帅哥” “2016001” “205” “120”,全部添加必须满足全部属性值

5.增加多条数据

​ 格式:insert into 表名 (列1,列2) values (值1,值2),(值1,值2);（指定增值）

		eg:insert into 学生(学号,姓名) values (110,'神滨海'), (121,'神马虎');

​ 格式:insert into 表名 values (值1,值2,值3),

​ (值1,值2,值3),

​ (值1,值2,值3),

​ (值1,值2,值3);（全部增值）

eg: insert into 学生 values (110,‘神滨海’,‘女’), (121,‘神马虎’,‘男’);

eg:insert into 学生 values (2016,‘花花’,‘女’), (20156,‘神’,‘男’);

6.修改数据(update)(表数据)

格式:update 表名 set 列1=值1:姓名=‘张三’,列2=值2 where 条件

eg:update 学生
set 学号=20160110,性别=‘女’
where 姓名=‘yy’;

7.删除表和表数据(drop,delete)

1.删除数据库

格式:drop database 数据库名;

eg:drop database sys;

2.删除表

格式:drop table 表名;

3.删除表数据

格式:delete from 表名 where 条件;

eg: delete from 神 where 姓名=‘张三‘；

8.alter的使用

1.修改表名:有两种:鼠标右击或者命令

格式:alter table 表名 rename to/as 新表名;

eg:alter table 学生 rename to 大帅哥;

2.向表中添加新的一列

格式:alter table 表名 add 列名 类型；

3.修改数据类型

格式:alter table 表名 modify 列名 新类型

4.修改列名和数据类型

格式:alter table 表名 change 旧列名 新列名 新类型

eg:alter table 大帅哥 change 学号 工号 int(10);

5.删除列

格式:alter table 表名 drop 列名;

eg:alter table 大帅哥 drop 年龄;

6.添加多列

eg:alter table 大帅哥 add 工 int;
alter table 大帅哥 add 生 int
;

9.查询语句(select)

1.select 字段1(姓名),字段2… from 表名(不带任何条件，结果是全部显示数据)

eg:select 工号,姓名 from 大帅哥;

2.*代表表中所有信息

eg:select * from 大帅哥;//全部数据显示

3.带条件的查询:select 字段1,字段2 from 表名 where 条件;

eg:select * from 大帅哥 where 性别=‘女’;

eg:select 姓名 from 教职工信息 where 年龄>25;

10.模糊查询(like)

1._代表1个数或者字

2.%代表一个或者多个数字或字

eg:查询姓张的

select 姓名,年龄 from 教职工信息 where 姓名 like ‘张%’;

或者select 姓名,年龄 from 教职工信息 where 姓名 like ‘张_’;

说明就是_号(张某某)

没有说明就是%（张）

日期需要打上单引号’2023-02-01’

charset utf8;设置字符集

eg： create database 名字 charset utf8;

字符集:create table dept(

…,

…,

…

)charset utf8;

Dup为重复值(错误)

11.修改默认值:

alter table 表名 alter 列名 set default 值1;

命令表：

show databases;//展示数据库，1

create database kk charset utf8;//新键数据库加字符集 2

create table 学生(
学号 int(10) primary key);//创建表 3

use//用

use kk;//用数据库 4

show tables;//展开表 5

12.范围查询

1.in();查询in中的数据信息(不连续 13 41 17 )

eg:in(15,17);查询的是15，17的数据;

格式:select 属性 from 表名 where 列名 in (条件1（19）,条件2（18）);

eg:select * from 学生 where 姓名 like ‘张%’ or 姓名 like ‘王%’ ;//姓王和姓张的数据

2.not in();查询不是in当中的数据信息.

eg:select * from 学生 where 年龄 not in (20);

3.范围分数查询 (between 条件1 and 条件2;)(连续范围内数据1-2)

3.1:> >= < <=;

3.2:select * from 学生 where 分数 between 70 and 80;

4.is null 查询为空信息（注意：先确保表中有空的，或者右击设为null ，才能查询到数据）

格式:列名 is null;

eg:select * from 学生 where 性别 is null;

5.is not null (查询不为空)

eg:select * from 学生 where 性别 is not null;

6.排序:order by(单字排序)(升序与降序)

desc:降序(从大到小)

asc:升序(从小到大)

结构:select 列1,列2 from 表名 where 条件 order by 列名 升序(降序);

eg:select * from 学生 order by 年龄 asc;

eg:select * from 学生 where 性别=‘女’ order by 年龄 asc;

7.猪和次的排序（多字段排序）

结构:select 显示的属性范围 from 表名 where 条 件 order by 列名 升序(降序),列2 升序(降序),列三 升序(降序)…;

eg:select * from 学生 where 性别=‘女’ order by 年龄 asc,姓名 asc;

13：多表查询

注意:笛卡尔积:将多个表数据进行组合;因为缺少内子啊连接条件

表名1.列名1=表名2.列名2

列名1来自表名1

列名2来自表名2

结构:select 字段名,字段名 from 表名1,表名2 where 表名对应字内在连接条件 and 内在连接条件

2个表=1个内在连接条件

3个表=2内在连接条件

4个表=3内在连接条件

5个表=4内在连接条件

方法1———》eg:select sno from tb_student where sname=‘张红’;
select grole from tb_gs where sno=‘s101’;

方法2———》eg:elect sname,grole from tb_student,tb_gs where tb_student.sno=tb_gs.sno;

方法3———》eg:select sname,grole,gname from tb_student,tb_gs,tb_group where tb_student.sno=tb_gs.sno and tb_gs.gro=tb_group.gno;(三表查询)

方法4———》eg:select sname,grole,gname from tb_student,tb_gs,tb_group where tb_student.sno=tb_gs.sno and tb_gs.gro=tb_group.gno and tb_group .gname=‘乒乓球社’;(明确条件)

十月准易调考数据库mysql

第6题:select count(*) from tb_gs,tb_group,tb_student where tb_student.sno=tb_gs.sno and tb_gs.gno=tb_group.gno and tb_group.gname=‘话剧社’ and tb_student.province=‘湖北’;

第7题:select tb_student.class,tb_student.sname,tb_gs.grole from tb_gs,tb_group,tb_student where tb_student.sno=tb_gs.sno and tb_gs.gno=tb_group.gno and
tb_group.gname=‘双节棍社’;

第8题:create view v_gs as
select t1.gno,t2.gname,count(*) from tb_gs t1 LEFT JOIN tb_group t2 on t1.gno=t2.gno GROUP BY t1.gno,t2.gname;

13.多表查询(外连接)

左外链接:显示表1当中有的所有数据

格式:select 字段列表,列表 from 表1 left join 表2 on 内在连接 where 条件;

eg:select tb_student.*,tb_gs.gno from tb_student LEFT JOIN tb_gs on tb_student.sno=tb_gs.sno ;

注意2:多表查询时，会省略字段为空的数据，用外连接解决

右外连接:显示表2当中有的所有数据

格式:select 字段值,字段值 from 表1 right join 表2 on 内在连接 where 条件;

(多个表就多个right join和left join)

eg:select tb_student.*,tb_gs.gno from tb_student RIGHT JOIN tb_gs on tb_student.sno=tb_gs.sno ;

自连接:把一个表分成两个表：select from 表1 as 新名字 ,表1 as 新名字（as可以省略)(通用)

格式:select 字段名 from 表名 新表名,表名 新表名 where 内在连接条件;

eg:zy.zzsno=zz.sno;

eg：select zy.sno,zy.sname,zz.sno,zz.sname from tb_student as zy,tb_student zz where zy.zzsno=zz.sno;

内连接(等值连接一样)

格式:select 字段列 from 表1 join 表2 join 表3 on 连接条件 where 条件;

eg:select t1.as from tb_from t1 join tb_sun t2 on t1.cno=t2.cno where t1.cun=‘5’；

创建视图:create view 名字 as

select …结构;

14.函数

聚合函数:将列作为整体，进行纵向运算

sum(字段);求和(比如数学一列的分数).eg:select SUM(rooms) from tb_booking t1 where t1.rno=‘r100’;

count(字段);求个数:计数(列值)

avg(字段);求平均数(列值)

max(字段);最大值(列值)

min(字段);求最小值(列值)

字符函数:操作字符

length(字段);计算字段中值的长度

concat(字段1,字段2,…)将所有字段连接在一起

LOWER(字段);将字段大写改为小写

upper(字段);将小写改大写；

链接加小写和大写转换eg:UPDATE tb_student set xingming=CONCAT(LOWER(firstname),UPPER(lastname))

substr(字段,开始位置,长度);截取长度。比如1234567 开始位是第3个开始,长度是4 ,结果是3456（列值）

mid(字段,开始位,长度);截取长度（列值）

长度省略，代表开始位置取后面的全部值都要取到（列值）。比如s123,

​ s12345。substr(sno,2)=123

​ =12345

instr(字段,子串);子串代表我们要求的东西;

定义:返回子串第一次出现的位置,如果找不到则返回数字0（返回位置数）

eg:instr(sno,‘g’);=isghtf ,‘g’=结果是数字3的位置

trim(字段);删除字段开头和结尾的空格

eg:select trim(tb_student.sname) from tb_student;

replace；替换全部列值

格式:replace(字段,被替换内容,替换内容);

eg:select replace(sno,‘s’,‘c’) from tb_student;

起表别名:(中文也可以)比如 from tb_gs 社团或者t1都行；

改列名: count(tb_group.gno) 参加总人数

分组函数:GROUP BY

分组函数方法1：

CREATE VIEW v_gs as
select tb_gs.gno,tb_group.gname,count(tb_group.gno) 参加总人数
from tb_gs,tb_group
where tb_gs.gno=tb_group.gno
GROUP BY tb_gs.gno;

分组函数方法2：CREATE VIEW v_ga AS
SELECT t2.gno,t1.gname,count(*) from tb_group t1 LEFT JOIN tb_gs t2 on t1.gno=t2.gno GROUP BY t1.gno,t1.gname;

聚合函数报错时，请添加头文件

set sql_mode=“NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES”;

eg:set sql_mode=“NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES”;
select score.cname 课程名 , MAX(grade) 最高分 from score GROUP BY score.cname;

内连接多表

select t1.cname,t3.name from score t1 JOIN goods t2 on t1.id=t2.id JOIN student t3 on t1.stuid=t3.stuid where t3.stuid=‘901’;

15.数值函数

ceil(字段);向上取整,取大于等于（>=）字段值;

eg:ceil (7.5)=8;

floor (字段);向下取整

eg:floor(7.5)=7;

round(字段,x);四舍五入之后保留x位小数,整数:去掉x或写0等于整数。

mod(字段,x);将字段除x求余数

RAND();动态验证码 ---->得到0-1之间的任意数（自动获取数值）

eg：rand()*10000=前四整数 4825.00

round(rand()*10000);=4825

日期函数

curdate();返回系统当前日期 年-月-日

curtime();返回系统当前时间 时分秒

not();返回系统当前的时间+日期

year(日期)=年

month(日期)=月

day(日期)=日

分页参数

limit:自成一行，没一页或展示多少条数据(限制多少条数据)

优先级：from>where>group by >haring>order by >limit

eg:select name,成绩 from student order by 成绩 desc limit 3;

其他函数

if(条件,真,假);判断,不能嵌套if，只能判断一次

多层判断

case when 条件 then 结果(真)

when 条件 then 结果

end（结束语句）；

解释:为假不能执行,到下一个when,下一个when …到真值 end 结束 (无需逗号结果)直接空格结果

子查询:可以不需要关联表

定义:在其它语句嵌套select语句

标量子查询:>,>=,<,<=,<>

结构:select 字段 from 表名 whsere 字段 >= (select 字段 from 表名 where 条件)；

列子查询:

in not in any/some all

eg:select sname,gender from tb_student where sno in(select sno from tb_gs where tb_gs.grole=‘副社长’);/in/

in:子查询得到的结果不止一个数值

not：不查询这个数值以外的数据;

all:必须满足子查询结果的所有记录

eg:select 姓名,身高
from 学生信息
where 身高 >= all (select max(身高) from 学生信息 where 班级=‘1班’);/all展示所有的信息，大于所有信息，就是最高的，<=所有信息就是最小的/

all=所有人的数据展示出来

any/some:满足子查询结果的一条记录(任意一条即可就成立)

eg:select 姓名,身高
from 学生信息
where 班级=‘1班’ and 身高 >= any (select 身高 from 学生信息 where 班级=‘2班’);/any满足其中一条就是满足,1班满足2班其中一条就是成立/

/select cname ,if(grade>=80,‘优秀’,‘良好’) 成绩情况 from score ; if 用法/
/*select cname,case when grade>=80 then ‘优秀’ ————多层判断
when grade>=70 then ‘良好’
when grade>=60 then ‘及格’
when grade<60 then ‘加油’
end 成绩情况
from score

;*/

聚合函数一般写在select 聚合函数 from 表名

神作

5.select * from tb_courses
order by convert(cid,signed) asc
limit 4,11 /4=下标-》4-1=5，，，，/

6.select stuname ,2023-year(birthday) from tb_students
where 2023-year(birthday)>30 and 2023-year(birthday)<=40

6.1/select stuname ,2023-year(birthday) from tb_students
where 2023-year(birthday)>30 and 2023-year(birthday)<=40/

select stuname ,TIMESTAMPDIFF(year,birthday,‘2023-12-01’) from tb_students
where TIMESTAMPDIFF(year,birthday,‘2023-12-01’)>=30 and TIMESTAMPDIFF(year,birthday,‘2023-12-01’)<=40

7.select stuname,gender,birthday,cname,score,
(CASE when score>=0 and score<=59 then ‘不及格’
when score>=60 and score<=69 then ‘及格’
when score>=70 and score<=79 then ‘中等’
when score>=80 and score<=89 then ‘良好’
when score>=89 then ‘优秀’ end)
FROM tb_students,tb_scores,tb_courses
where tb_students.stuid=tb_scores.student_id
and tb_courses.cid=tb_scores.course_id
ORDER BY score desc;

8.update tb_books set note=trim(SUBSTRING_INDEX(note,‘_’,-1))
update tb_books set note=trim(note)
/* select SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2); 正负数,整数往前面返回，负数往右边返回*/

9.select t2.book_id,t2.price,t3.author_id,t3.author_name,t2.publication_date
from tb_book_authors t1 JOIN tb_books t2 on t1.book_id=t2.book_id JOIN tb_authors t3 on t3.author_id=t1.author_id

where year(t2.publication_date)>=1980 and
year(t2.publication_date)<=2010
order by t2.publication_date desc

10. CREATE VIEW v_book AS
    SELECT b.genre, a.author_name, COUNT() AS book_count
    FROM tb_books b
    JOIN tb_book_authors ba ON b.book_id = ba.book_id
    JOIN tb_authors a ON ba.author_id = a.author_id
    GROUP BY b.genre, a.author_name
    HAVING COUNT() = (
    SELECT COUNT()
    FROM tb_books b2
    JOIN tb_book_authors ba2 ON b2.book_id = ba2.book_id
    WHERE b2.genre = b.genre
    GROUP BY ba2.author_id
    ORDER BY COUNT() DESC
    LIMIT 1
    );

11.update test1 JOIN (select tname,concat(tname,‘@’,count(tname)) as ij from test1
GROUP BY tname
HAVING count(tname)=3 or count(tname)=4) as a
on test1.tname=a.tname
set test1.tname= a.ij

update student JOIN
(select a.s_id,concat(a.s_name,“-”,count(b.c_id)) as j1
from student a left JOIN score b on a.s_id=b.s_id
GROUP BY a.s_id) as j2
on student.s_id=j2.s_id
set student.notes=j2.j1

select any_value(b.c_id),any_value(a.c_name),
count(case when s_score between 85 and 100 then 1 end) as “85——100”,
count(case when s_score between 84 and 71 then 1 end) as “85——75”,
count(case when s_score between 70 and 60 then 1 end) as “75-60”,
count(case when s_score<60 then 1 end) as “<60”

from course a JOIN score b on a.c_id=b.c_id
GROUP BY a.c_id

100 then 1 end) as “85——100”,
count(case when s_score between 84 and 71 then 1 end) as “85——75”,
count(case when s_score between 70 and 60 then 1 end) as “75-60”,
count(case when s_score<60 then 1 end) as “<60”

from course a JOIN score b on a.c_id=b.c_id
GROUP BY a.c_id

[外链图片转存中…(img-EYv4O4Mo-1703159349818)]

[外链图片转存中…(img-PpcVho7T-1703159349818)]

[外链图片转存中…(img-h6dlxYJc-1703159349818)]

[外链图片转存中…(img-ZgDm51un-1703159349818)]

[外链图片转存中…(img-IhEeCIaT-1703159349818)]

[外链图片转存中…(img-k9ZRnYKD-1703159349818)]

[外链图片转存中…(img-PHraJFNF-1703159349819)]

[外链图片转存中…(img-1YJPcSdP-1703159349819)]

[外链图片转存中…(img-ml9V3fb0-1703159349819)]

注意：全部选中