MySQL 常用语句
#Navicat For Mysql快捷键
-- 1.ctrl+q 打开查询窗口
-- 2.ctrl+/ 注释sql语句
-- 3.ctrl+shift +/ 解除注释
-- 4.ctrl+r 运行查询窗口的sql语句
-- 5.ctrl+shift+r 只运行选中的sql语句
-- 6.F6 打开一个MySQL命令行窗口
-- 7.ctrl+l 删除一行
-- 8.ctrl+n 打开一个新的查询窗口
-- 9.ctrl+w 关闭一个查询窗口
#ddl-数据库定义语言
#create、alter、drop DATABASE、table、view
#新建数据库
-- create database test_mysql;
#新建student_info表
-- CREATE TABLE student_info (
-- stu_idINT,
-- NAMENVARCHAR (8),
-- sexNVARCHAR (4),
-- classNVARCHAR (16)
-- );
区分：nvarchar是按字符(双字节)存储的，而 varchar是按字节存储的；
#新建teacher_info表
#包含的字段有：教师号（tea_id）、姓名(name)、性别（sex）、入职时间（join_time）、民族（nationality）。
#教师号为主键，具有非空和唯一的属性。性别只有男或女两种选择，所以使用check约束。民族默认为汉族。
-- CREATE TABLE teacher_info (
-- tea_idINT PRIMARY KEY,
-- NAMEVARCHAR (8),
-- sexVARCHAR (4) CHECK (
-- sex= ’男’
-- ORsex = ’女’
-- ),
-- join_timedate,
-- nationalityNVARCHAR (10) DEFAULT '汉族'
-- );
#新建课程信息表(course_info)
#包含课程编号(course_id)、课程名称(course_name)其中课程编号为主键。
-- create tablecourse_info (
-- course_idINT primary key,
-- course_nameNVARCHAR (20)
-- -- )
#新建学生成绩表(student_score)
#包含学号 (stu_id) 、课程编号(course_id) 、成绩 (score) 其中主键为学号和课程编号两个字段。
-- CREATE TABLE student_score (
-- stu_idINT,
-- course_idINT,
-- scoreFLOAT,
-- primary key(stu_id, course_id)
-- );
#新建视图
-- CREATE VIEW student_score_v AS SELECT
-- a.stu_idAS 学生ID,
-- a.NAME AS 学生姓名,
-- a.sexAS 学生性别,
-- a.classAS 专业名称,
-- c.course_nameAS 课程名称,
-- b.scoreAS 分数
-- FROM
-- student_infoa
-- LEFT JOINstudent_score b ON a.stu_id= b.stu_id LEFT JOIN course_info c ON b.course_id = c.course_id ;
--
--
-- select * from student_score_v
#修改表结构：在学生表(student_info)中添加一个字段，记录学生的电话号码（tel_num）
-- alter table student_infoadd tel_numint(11);
#修改表结构：将tel_num改为ste_tel
-- alter tablestudent_infochangetel_numste_telvarchar(8);
#修改字段属性：把stu_id设置为主键
-- alter tablestudent_infoMODIFYstu_id INT primarykey;
#删除字段ste_tel
-- alter table student_infodrop column ste_tel;
#DML-数据库操作语言
#select、insert、update、delete
#向学生信息表 (student_info)中插入学生的信息
-- INSERT INTOstudent_info
-- VALUES
-- (
-- 20080201,
-- '张帅',
-- '男',
-- '计算机软件'
-- );
#插入多条数据
-- INSERT INTO student_info
-- VALUES
-- (
-- 20080202,
-- '张帅',
-- '男',
-- '计算机软件'
-- ),(
-- 20080203,
-- '张不帅',
-- '女',
-- '计算机应用'
-- ),(
-- 20080204,
-- '张很帅',
-- '男',
-- '计算机网络'
-- ),(
-- 20080205,
-- '张非常帅',
-- '女',
-- '计算机网络'
-- );
#插入课程表信息course_info
-- INSERT INTOcourse_info
-- VALUES(10001,'高等数学'),(10002,'大学英语'),(10003,'大学语文')
#插入分数表student_score
-- INSERT INTO student_score
-- VALUES
-- (20080201,10001,89),
-- (20080202,10002,99),
-- (20080203,10003,89),
-- (20080204,10003,69);
#修改学生信息表(student_info)
#张非常帅转专业了
-- UPDATEstudent_info
-- SET class= '计算机软件'
-- WHERE
-- NAME= '张非常帅';
#删除某学生的信息
#张不帅退学了
-- delete from student_infowhere name='张不帅';
#单表查询所有学生的个人信息，并按照学号排序
-- SELECT
-- *
-- FROM
-- student_info
-- ORDER BY
-- stu_id;
#单表查询李飞同学的学号和所在的班级
-- SELECT
-- stu_idAS 学号,
-- classAS 班级
-- FROM
-- student_info
-- WHERE
-- NAME= '张很帅';
#单表查询查询学生信息表中的记录数
-- SELECT
-- count(*)
-- FROM
-- student_info;
#分组查询：查询学生信息表中各班男女学生的人数
-- SELECT
-- class,
-- sex,
-- count(*)
-- FROM
-- student_info
-- GROUP BY
-- class,
-- sex;
#单表查询是否有名叫张非常帅的女生
-- SELECT
-- *
-- FROM
-- student_info
-- WHERE
-- NAME= '张非常帅'
-- AND sex = '女';
#查询男生总数
-- SELECT
-- count(*)AS '男生总数'
-- FROM
-- student_info
-- WHERE
-- sex= '男';
#联合查询：1) 查询指定班级，课程的平均分
-- SELECT
-- avg(c.score) AS 平均分
-- FROM
-- student_infoa,
-- course_infob,
-- student_scorec
-- WHERE
-- a.stu_id= c.stu_id
-- AND b.course_id = c.course_id
-- AND b.course_name = '计算机网络'
-- AND a.class = '计算机网络';
# 2) 查询各门课程的平均分
-- SELECT
-- a.course_name,
-- avg(score)
-- FROM
-- course_infoa,
-- student_scoreb
-- WHERE
-- a.course_id= b.course_id
-- GROUP BY
-- a.course_id,
-- a.course_name;
# 3) 查询大学英语的最高分和最低分
-- SELECT
-- max(score)AS ‘最高分’,
-- min(score)AS ‘最低分’
-- FROM
-- course_infoa,
-- student_scorec
-- WHERE
-- a.course_id= c.course_id
-- AND a.course_name = '计算机网络';
-- SELECT
-- *
-- FROM
-- student_score_va
-- WHERE
-- (a.分数 BETWEEN 85AND 99)
-- AND a.课程名称<> '计算机软件'
-- AND a.课程名称 LIKE '%应用'
-- AND a.专业名称 IN (
-- '计算机软件',
-- '计算机网络'
-- )
-- AND a.学生性别 IS NOT NULL
#函数
#数字函数
-- ABS(x)
-- 返回x的绝对值
-- SELECT ABS(-1) -- 返回1
-- RAND()
-- 返回0->1的随机数
-- SELECT RAND()
#字符串函数
-- CHAR_LENGTH(s)
-- 返回字符串s的字符个数：
-- SELECT CHAR_LENGTH('你好123') -- 5
-- LENGTH(s)
-- 返回字符串s的长度：
-- SELECT LENGTH('你好123') – 9
注：MySQL的length的函数，中文一个汉字的长度返回3。
#CONCAT(s1,s2,...)
-- SELECT CONCAT('12','34') -- 1234
#INSERT(s1,x,len,s2)
#用s2替换s1；( x开始位置, len数量)
-- SELECT INSERT('12345',1,2,'abc') -- abc345
#UPPER(s)
#将字符串s的字母大写
-- SELECT UPPER('abc') -- ABC
#LOWER(s), LCASE(s)
#将字符串s的所有字母变成小写字母
-- SELECT LOWER('ABC') -- abc
#LEFT(s,n)
#返回字符串s的前n个字符
-- SELECT LEFT('abcde',2) -- ab
#RIGHT(s,n)
#返回字符串s的后n个字符
-- SELECT RIGHT('abcde',2) -- de
#LPAD(s1,len,s2)
#用s2来填充s1的开始处，使字符串长度达到len
-- SELECT LPAD('abc',8,'123456')-- 12345abc
#LTRIM(s) 去掉字符串s开始处的空格
-- select LTRIM(' sada ')
#RTRIM(s) 去掉字符串s结尾处的空格
-- select RTRIM(' sada ')
#TRIM(s) 去掉字符串s开始和结尾处的空格
-- select TRIM(' sada ')
#TRIM(s1 FROM s)
#去掉s中首尾处的字符串s1
-- SELECT TRIM('@' FROM '@@[email protected]@') -- abc
#REPEAT(s,n)
#将字符串s重复n次
-- SELECT REPEAT('ab',3) -- ababab
#SPACE(n) 返回n个空格
-- select SPACE(2)
#REPLACE(s,s1,s2)
#用s2替换s中的字符串s1
-- SELECT REPLACE('刘豪最帅','刘豪','赵一峰')
#STRCMP(s1,s2) 比较字符串s1和s2
-- SELECT STRCMP('刘豪最帅','赵一峰最帅')
-- SELECT STRCMP('刘豪最帅','刘豪最帅')
#MID(s,n,len) 同SUBSTRING(s,n,len)
字符串截取
-- select MID('刘豪最帅',1,2)
-- select SUBSTRING('刘豪最帅',1,2)
#时间日期函数
#CURDATE(),CURRENT_DATE()
#返回当前日期
-- SELECT CURDATE()
#CURTIME(),CURRENT_TIME
#返回当前时间
-- SELECT CURTIME()
#NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),
#SYSDATE(),LOCALTIMESTAMP()
#返回当前日期和时间
-- SELECT NOW()
-- SELECT SYSDATE()
#UNIX_TIMESTAMP()
#以UNIX时间戳的形式返回当前时间
-- SELECT UNIX_TIMESTAMP()
-- SELECT UNIX_TIMESTAMP('2017-07-28')
#FROM_UNIXTIME(d)
#将UNIX时间戳的时间转换为普通格式的时间
-- SELECT FROM_UNIXTIME(1320981071)
#EXTRACT(type FROM d)
#从日期d中获取指定的值，type指定返回的值
-- SELECT EXTRACT(MINUTE FROM '2010-11-1314:15:16')
-- SELECT EXTRACT(DAY FROM '2010-11-1314:15:16')
-- SELECT EXTRACT(YEAR_MONTH FROM'2010-11-13 14:15:16')
-- SELECT EXTRACT(HOUR_MINUTE FROM'2010-11-13 14:15:16')
-- type可取值为：
--
-- MICROSECOND
-- SECOND
-- MINUTE
-- HOUR
-- DAY
-- WEEK
-- MONTH
-- QUARTER
-- YEAR
-- SECOND_MICROSECOND
-- MINUTE_MICROSECOND
-- MINUTE_SECOND
-- HOUR_MICROSECOND
-- HOUR_SECOND
-- HOUR_MINUTE
-- DAY_MICROSECOND
-- DAY_SECOND
-- DAY_MINUTE
-- DAY_HOUR
-- YEAR_MONTH
#条件判断函数
#1、IF(expr,v1,v2)函数如果表达式expr成立，返回结果v1；否则，返回结果v2。
-- SELECT IF(1 > 0,'正确','错误')
#2、IFNULL(v1,v2)函数如果v1的值不为NULL，则返回v1，否则返回v2。
-- SELECT IFNULL(null,'Hello Word')
-- SELECT IFNULL(null,'')
--
#3、CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，
#而当有一个成立之后，后面的就不执行了。
#CASE
# WHEN e1
# THEN v1
# WHEN e2
# THEN e2
# ...
# ELSE vn
#END
-- SELECT CASE
-- when 1 > 0 THEN '1 > 0'
-- when 2 > 0 THEN '2 > 0'
-- ELSE '3 > 0'
-- END
注：类似于switch条件判断语句。
#语法2：
#CASE expr
# WHEN e1 THEN v1
# WHEN e1 THEN v1
# ...
# ELSE vn
#END
#如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。
-- SELECT CASE 1
-- when 1 THEN '我是1'
-- when 2 THEN '我是2'
-- ELSE '你是谁' END