mysql

# 笛卡尔积
SELECT `name`,boyName FROM beauty,boys;

/*
表1有m行 表2有n行
结果有m*n行

发生原因:没有有效的连接条件
避免:添加有效的连接条件

    按年代分类 
        sql92标准仅仅支持内连接
        sql99标准支持内连接、外链接(左外,右外),交叉连接
    按功能分类
        内连接:
            等值连接
            非等值连接
            自连接
        外链接
            左外连接
            右外链接
            全外连接(mysql不支持)
        交叉连接
            
            

*/
# 解决方法
SELECT `name`,boyName FROM beauty,boys
WHERE beauty.`boyfriend_id`=boys.`id`;


#sql92标准
    #等值连接
    #查询女神名和对应男生名
    SELECT `name`,boyName
    FROM boys,beauty
    WHERE boys.`id`=beauty.`boyfriend_id`;
    

    #查询员工名和对应的部门名
    SELECT last_name,department_name
    FROM employees,departments
    WHERE employees.department_id=departments.department_id;
    
    # 查询员工名,工种号,工种名
    SELECT last_name,e.job_id,job_title
    FROM employees e,jobs j
    WHERE e.job_id=j.job_id;
    
    
    # 查询城市名中第二个字符为o的部门名和城市名
    
    SELECT department_name,city
    FROM departments ,locations
    WHERE departments.location_id=locations.location_id
    AND city LIKE "_o%";
    
    # 查询每个城市部门个数
    
    SELECT COUNT(*) 个数 ,city
    FROM departments d,locations l
    WHERE d.location_id=l.location_id
    GROUP BY l.city;
    
    # 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
    SELECT d.department_name,e.manager_id,MIN(e.salary) 最低工资
    FROM departments d,employees e
    WHERE d.department_id=e.department_id
    AND e.commission_pct IS NOT NULL
    GROUP BY department_name
    
    # 查询每个工种的工种名和员工个数,并且按员工个数降序
    SELECT job_title,COUNT(*)
    FROM employees e,jobs j
    WHERE e.job_id=j.job_id
    GROUP BY j.job_title
    ORDER BY COUNT(*) DESC;
    
    # 查询员工名,部门名和所在的城市
    SELECT last_name , department_name , city
    FROM employees e, departments d, locations l
    WHERE e.department_id=d.department_id AND d.location_id=l.location_id;
    
    
    /*
        多表等值连接的结果为多表的交集部分
        n表连接至少需要n-1个连接条件
        多表的额顺序没有要求
        一般需要为表取别名
        可以搭配所有字句使用
            排序     分组  筛选
    */
    
    
    
    
    # 非等值连接
    
    # 查询员工的工资和工资级别
    
    SELECT salary ,grade_level
    FROM employees e ,job_grades g
    WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
    
    
    # 自连接

    # 查询员工名和上级的名称
    SELECT e1.last_name '员工名',e2.last_name '领导名'
    FROM employees e1,employees e2
    WHERE e1.manager_id=e2.employee_id;
    
    
    # 显示员工表最大工资,工资平均值
    SELECT MAX(salary) , AVG(salary) 
    FROM employees;
    
    
    # 查询员工表的employee_id ,job_id,last_name 按department_id降序,salary 升序
    SELECT employee_id ,job_id,last_name
    FROM employees
    ORDER BY department_id DESC,salary ASC;
    
    
    # 查询员工表的job_id中包含a和e的,并且a在e前面的
    SELECT job_id 
    FROM employees 
    WHERE job_id LIKE '%a%e%'
    
    # 已知表student,里面有id name gradeId
    # 已知表grade 里面有id name
    #已知表result里面有 id score  studentNo
    #要求查询姓名,年级名,成绩
    SELECT s.name, g.name,r.score
    FROM studen s,grade g,result r
    WHERE s.gradeId=g.id AND s.id=r.studentNo;
    
    
    # 显示当前日期,以及去前后空格,截取子字符串的函数
    
    SELECT NOW();
    SELECT TRIM("    hhhh :-- "); #取出指定字符   trim(字符 from ' ')
    SELECT SUBSTR("helloworld",2);
    
    SELECT USER();
    SELECT RAND()# 返回随机数
    SELECT PASSWORD ('root')
    
    
    # 内连接
    /*select 查询列表
    from 表1 别名
    inner join 表2 别名
    on 连接条件
    where 筛选条件 
    
    */
    
    
    
SELECT e.job_id,d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id
AND e.department_id=90;






# sql99语法

/*
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 排序列表]
[order by 排序列表]

    内连接  inner join
    外链接
        左外 left [outer] join
        右外 right [outer] join
        全外 full [outer] join
    交叉连接 cross join

*/

# 内连接
/*
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;


添加排序,分组,筛选
inner 可以省略
筛选条件放在where后面,链接条件房子on后面 便于阅读

inner join 连接和sql92连接查询的结果是一样的  都是查询交集
 */


# 查询员工名,部门名
SELECT e.last_name , d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;


# 查询名字中包含e的员工名和工种名
SELECT e.last_name,j.job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';




# 查询部门个数>3的城市名和部门个数

SELECT l.city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY l.city
HAVING COUNT(*) >3


# 查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(添加排序)

SELECT department_name,COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
GROUP BY department_name
HAVING COUNT(*) >3
ORDER BY COUNT(*) DESC;
 
# 查询员工名,部门名,工种名,并按部门名降序   三表连接查询 
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON  e.department_id=d.department_id
INNER JOIN jobs j
ON e.job_id= j.job_id
ORDER BY department_name DESC




# 非等值连接

# 查询员工的工资级别
SELECT salary ,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;


# 查询每个工资级别>20的个数,并且按照工资的级别降序
SELECT  grade_level,COUNT(*) 个数
FROM job_grades g
JOIN employees e
ON  e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC


# 自连接
# 查询员工的名字,上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.employee_id=m.manager_id
WHERE e.last_name LIKE "%k%"


# 外链接

/*
应用场景:用于查询一个表中有,另一个表中没有的记录。

特点:
    外链接的查询结果为主表中的所有记录
    如果从标中有和它匹配的,则显示匹配的值
    如果从小中没有和它匹配的,则显示null
    外链接查询的结果=内连接结果+主表中有而从表中没有的记录
    
外连接:
    left join : left左边的是主表
    right join : right join右边的是主表
左外和右外交换两个表的顺序,可以实现同样的结果

    
*/


# 查询 


SELECT COUNT(*)
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

SELECT b.`name`,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;




# 交叉连接 相当于sql92的笛卡尔积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

#查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充

SELECT b.name,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3


# 查询哪个城市没有部门
USE myemployees
SELECT city
FROM departments d
RIGHT JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_name IS NULL



# 查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE department_name='SAL' OR department_name='IT'


# 子查询
/*
含义:
出现在其他语句中的select 称之为子查询或内查询

外部的查询语句,称为主查询或外查询

分类:
    按子查询出现的位置:
        select后面 
            仅仅支持标量子查询
        from后面
            支持表子查询
        where或having后面
            标量子查询
            列子查询
            行子查询
        exists后面(相关子查询)
            表子查询
    按结果集的行列数不同:
        标量子查询(结果集只有一行一列)
        列子查询(结果集只有一列多行)
        行子查询(结果集有一行多列)
        表子查询(结果集一般为多行多列)
        
*/

# 1、标量子查询
# 查询工资高于Abel的员工信息
SELECT * 
FROM employees
WHERE salary >
    (SELECT salary
    FROM employees 
    WHERE last_name='Abel');


# 返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id =
    (
        SELECT job_id 
        FROM employees 
        WHERE employee_id=141
    ) AND
salary > 
    (
        SELECT salary 
        FROM employees
        WHERE employee_id=143
    );


# 返回工资工资最少的员工的last_name  jon_id salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=
    (
        SELECT MIN(salary)
        FROM employees
        
    );




# 查询最低工资大于50号部门最低工资的部门id和其最低工资

# ①50号部门最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id =50


SELECT department_id,MIN(salary) 最低工资
FROM employees
GROUP BY department_id
HAVING
    MIN(salary)>(
        SELECT MIN(salary)
        FROM employees
        WHERE department_id =50
    );



# 多行子查询
/*
返回多行
使用多行比较操作符
in/not in    等于列表中的任意一个
any|some  和子查询返回的某一个值比较
all 和子查询返回的所有指标较

*/

# 返回location_id是1400或1700的部门中所有的员工姓名

SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT department_id
    FROM departments
    WHERE location_id=1400 OR location_id =1700
    #where location_id in(1400,1700)

)

# 返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

# ① 先查询job_id为‘IT_PROG’ 所有工资
SELECT salary
FROM employees
WHERE job_id='IT_PROG'



SELECT employee_id ,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
    SELECT salary
    FROM employees
    WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';



# 返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary



SELECT employee_id ,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
    SELECT salary
    FROM employees
    WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';



# 查询员工编号最小并且工资最高的员工信息

# 最简单直接的方式,两个子查询解决
SELECT * FROM 
employees
WHERE employee_id = (SELECT MIN(employee_id)FROM employees)
AND salary = (SELECT MAX(salary)FROM employees)

# 行子查询
SELECT  * 
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)FROM employees
)



# select后面的子查询

# 查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*) FROM employees  e WHERE e.department_id=d.department_id) 个数
FROM departments d;


# 查询员工号=102的部门名
# 子查询实现
SELECT department_name FROM departments WHERE department_id=(
SELECT department_id FROM employees
WHERE employee_id=102
)

# 连接查询实现
SELECT department_name
FROM departments d 
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102;


#from 后面

# 查询每个部门的平均工资的平均等级 sql92
SELECT ag_dep.*,grade_level FROM 
(SELECT AVG(salary) s , department_id
FROM employees
GROUP BY department_id) ag_dep, job_grades j
WHERE ag_dep.s BETWEEN j.lowest_sal AND highest_sal



SELECT ag_dep.*,grade_level FROM 
    (
    SELECT AVG(salary) s , department_id
    FROM employees
    GROUP BY department_id
    ) ag_dep
INNER JOIN job_grades j
ON ag_dep.s BETWEEN j.lowest_sal AND highest_sal




# exists后面  判断数据是否存在
SELECT EXISTS(SELECT  employee_id FROM employees )

# 查询有员工名和部门名
# 方式一
SELECT department_name
FROM departments d
WHERE d.department_id IN (
    SELECT e.department_id FROM employees e
)

# 方式二
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT e.department_id FROM employees e
    WHERE e.department_id=d.department_id
    )

#查询和Zlotkey相同部门的员工和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name='Zlotkey')


# 查询工资比公司平局工资高的员工的员工号,姓名和工资

SELECT  employee_id,last_name,salary
FROM employees
WHERE salary> (SELECT AVG(salary) FROM employees);


# 查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
 
#各部门平均工资
SELECT department_id , AVG(salary) ag
    FROM employees
    GROUP BY department_id



SELECT employee_id ,last_name,salary
FROM employees e
JOIN 
    (
    SELECT department_id , AVG(salary) ag
    FROM employees
    GROUP BY department_id
    )ag_dep

ON e.department_id=ag_dep.department_id
WHERE 
salary>ag_dep.ag




# 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名


SELECT employee_id ,last_name
FROM employees
WHERE department_id IN(
    SELECT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
)




# 查询在部门location_id为1700的部门工作的员工的员工号
SELECT employee_id 
FROM employees
WHERE department_id IN(
    SELECT department_id FROM departments
    WHERE location_id =1700
)

# 查询管理者是K_ing的员工姓名和工资
SELECT  last_name ,salary
FROM employees
WHERE manager_id IN 
(SELECT employee_id FROM employees WHERE last_name='K_ing')

#查询工资最高的员工姓名,要求first_name和last_name显示为一列
#第一种方式
SELECT CONCAT(last_name,first_name)
FROM employees
WHERE salary =(SELECT MAX(salary) FROM employees)


#第二种方式
SELECT CONCAT(last_name,first_name)
FROM employees
ORDER BY salary DESC
LIMIT 0,1;



/*
limit offset ,size;

offset 要显示的起始索引(从0开始)
size 每页显示的条数

offset=(当前页-1)*每页要显示的条数
2-1 *5

limit 0,5;
limit 5;  结果一样  都是查询前5条

特点;limit语句房子查询语句的最后,执行顺序也在最后

*/

# 有奖金的员工信息,并且显示工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;











# 查询工资最低的员工信息:last_name,salary
SELECT last_name,salary
FROM employees 
WHERE salary=(SELECT MIN(salary)FROM employees);
#查询平均工资最低的部门信息
SELECT d.*,se.avg_sal
FROM departments d
JOIN 
(SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) se

ON d.department_id=se.department_id
ORDER BY se.avg_sal
LIMIT 0,1

#查询平均工资最低的部门信息和该部门的平局工资
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0,1;


#查询平均工资最高的job信息
# 方式一
SELECT j.*
FROM jobs j
WHERE j.job_id =
(SELECT job_id  FROM employees e GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 0,1) 
 
 #方式二
 SELECT j.* FROM jobs j
 WHERE j.job_id =(
    SELECT job_id FROM employees s
    WHERE salary =(
        SELECT MAX(a.s)
        FROM (SELECT job_id j , AVG(salary)s FROM employees e GROUP BY job_id) a
     ) 

 )






/*

union 联合查询

union 联合  合并:将多条查询语句的结果合并成一个结果


语法:
查询语句1
union
查询语句2

应用场景:
要查询的结果来自多个表,且多个表没有直接的连接关系,就是用连接查询
特点:
    要求多条查询语句的列数是一致的
    要求多条查询语句查询的每一列的顺序和类型是一致的
    
    使用union关键字默认是去重的
    union all一包含重复项
    
    
*/

# 查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id >90;

#联合查询方式
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id >90;


# 查询中国用户中男性的信息以及外国用户中男性的用户信息

SELECT id,cname, csex FROM t_ca WHERE csex="男"
UNION
SELECT t_id,tName, tGender FROM t_ua WHERE tGender="male"




/*
DML语言  

    数据操纵语言
    数据的插入修改删除等操作
    insert update delete
*/

#插入语句

/*
语法:
    insert into 表名(列名1,列名2 ...) values(值1,值2....);
    
    
    
    
    
    
*/
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 
VALUES(13,'tony','','1998-04-09','18605647601',NULL,2);

# 可以为null的列如何插入值?
# ①直接插入null值  ② 不插入可谓null 的字段
#不可以为null的列必须插入值

#方式二
INSERT INTO 表名(部门字段名) VALUES(部分值);

#方式三 字段名顺序可以与表的顺序不一致,但要与值的顺序一致

# 方式四 字段个数要与值的个数一致

# 方式五
#可以省略列名 默认所有列,而且列的顺序和表中列的顺序一致

INSERT INTO beauty
VALUES(18,'张飞','',NULL,'119',NULL,NULL)

#支持插入多行
INSERT INTO beauty
VALUES(18,'张飞','',NULL,'119',NULL,NULL),
VALUES(18,'张飞','',NULL,'119',NULL,NULL),
VALUES(18,'张飞','',NULL,'119',NULL,NULL);
/*
语法二:
insert into 表名
set 列名=值 ,列名=值,...

*/

INSERT INTO beauty 
SET id=20,NAME='tom',phone='234';




/*

语法一支持插入多行,方式二不支持
语法一支持子查询,方式二不支持
*/

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'jack','111';


/*
修改单标的记录
语法:
    update 表名
    set 列=新值,列=新值...
    where 筛选条件       
*/

UPDATE beauty SET phone='199999999999'
WHERE NAME LIKE '唐%'

# 多表记录的修改
UPDATE boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
SET b.phone='114'
WHERE bo.boyName='张无忌'



# 修改没有男朋友的女神的男朋友编号为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
SET b.boyfriend_id=2
WHERE b.boyfriend_id IS NULL;

SELECT b.* , bo.* FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`

 
 # 删除语句
 /*
 
语法一:delete
语法:
delete from 表名 where 
语法:
    多表的删除
delete  表1的别名 ,表2的别名
from 表1 别名 ,表2 别名    
where 连接条件
and 筛选条件


delete  表1的别名 ,表2的别名
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件;

    
    方式二:

truncate table 表名
 */
# 删除手机号一9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9'; 
 
 
 
 # 多表的删除
  # 删除张无忌的女朋友的信息
 DELETE b FROM
 beauty b 
 INNER JOIN boys bo
 ON b.`boyfriend_id`=bo.`id`
 WHERE bo.`boyName`='张无忌';
 
 # 删除黄晓明的信息以及他女朋友的信息
 DELETE b,bo
 FROM beauty b
 INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
 WHERE bo.`boyName`='黄晓明';
 
 #方式二 truncate语句
 TRUNCATE TABLE boys;  
 
 
 /*
delete 与 truncate 
 
 
 delete能加限制条件   truncate不能加
 
 truncate删除效率较高
 
 使用delete删除后,不会删除之前主键的记录
 truncate会删除历史记录,再次插入数据 主键从1开始
 
 truncate删除没有返回值 delete删除有返回值
 
 truncate 删除不能回滚,delete杀出可以回滚
 */
 
 
 
 /*
DDL  数据定义语言 

库、表
创建、修改、删除 

创建: create
修改 alter
删除 drop
 
 
 */
 
 # 创建库  IF NOT EXISTS
 CREATE DATABASE IF NOT EXISTS books;
 
 #库的修改
 
 
 RENAME DATABASE  books TO 新库名;
 
 
 # 更改库的字符集
 ALTER DATABASE books CHARACTER SET gbk;
 
 #库的删除
 DROP DATABASE IF EXISTS books;
 
 # 表的创建

CREATE TABLE 表名(
    列名 类类型,
    列名 类类型,
    列名 类类型
)
 
 # 创建book表
 USE books;
 CREATE TABLE book(
    id INT, # 编号
    bname VARCHAR(30), # 图书名
    price DOUBLE, #价格
    authorId INT, #作者编号
    publishDate DATETIME #出版日期
    
 );
 
 
 
 
 
 # 创建表author
 CREATE TABLE author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(10)
 )
 
 DESC author;
 
 # 表的修改
 #修改列名     COLUMN可省略
 ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
 
 DESC book;
 #修改列的类型或约束
 ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
 
 DESC book;
 #添加列
 ALTER TABLE author ADD COLUMN annual DOUBLE;
 #删除列
 ALTER TABLE author DROP COLUMN annual;
 #修改表名
 ALTER TABLE author RENAME TO book_author;
 
 
 #表的删除
 DROP TABLE IF EXISTS author;
 
 #查看当前库的所有表
 SHOW TABLES;
 
 
 
 #通用的写法
 
 DROP DATABASE IF EXISTS 旧库名;
 
 CREATE DATABASE 新库名;
 
 DROP TABLE IF EXISTS 旧表名;
 
 CREATE TABLE 表名(
    ...
    
 );
 
 
 
 # 表的复制
 # 仅复制表的结构
 CREATE TABLE copy LIKE author;
 
 
 #复制表的结构+数据
 CREATE TABLE copy2
 SELECT * FROM author;
 

#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';


# 仅复制某些字段
# 过滤条件不成立时,只会复制字段,数据不会复制到表中

CREATE TABLE cpoy4
SELECT id,au_name
FROM author
WHERE 1=2;



#常见的数据类型


/*

数值型:整型
    小数:
        定点数
        浮点数
    字符型
        较短的文本 char、varchar
        
        较长的文本 text  blob(较长的二进制数据)
    日期型
    
整型:
    如果不设置无符号还是有符号,默认是有符号  设置无符号 unsigned
    如果插入的数值超出了整型的范围,会报out of range异常并且 插入 如临界值
    
    
    
    
分类:
    浮点数
    float(M,D)   float(5,2)   M 整数+小数部分   D小数部分
    double(M,D)
    定点型
    dec(M,D)  M默认为10  D默认为0
    decimal(M,D)
*/



/*

字符型
    较短的文本
        char(M)      定长的字符   默认长度为1
        varchar(M)   M 最多字符数    可变长度的字符  长度不可以省略
    较长的文本
        text
        blob(较大的二进制)


*/


CREATE TABLE tab_char(
    c1 ENUM('a1','b','c')
);


 
 INSERT INTO tab_char VALUE('a'); 
  INSERT INTO tab_char VALUE('b');
   INSERT INTO tab_char VALUE('c');
    INSERT INTO tab_char VALUE('m');  #无法插入
     INSERT INTO tab_char VALUE('A'); -- 插入的值仍为a
 
 
 CREATE TABLE tab_set(
    s1 SET( 'a','b','c','d')
 
 );
 
INSERT INTO tab_set VALUE('a');
INSERT INTO tab_set VALUE('a,b');
INSERT INTO tab_set VALUE('a,b,c'); -- 都可以插入成功

 
 
 /*
日期型
date 只有日期 没有时间
datetime  有日期有时间 
timestamp  时间戳
 time  只有时间 没有日期
 year 只有年
 */
 
 CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP 
 );
 
 INSERT INTO tab_date VALUE(NOW(),NOW());
 
 
 SHOW VARIABLES LIKE 'time_zone';
 SET time_zone='+9:00';
 
 
 
 
 
 
/*
常见约束
含义:一种限制,用于限制表中的数据,为了保证表中数据准确和可靠性

分类:六大约束

    not null 非空约束,用于保证该字段的值不能为空
        比如 姓名,学号等
    default 默认约束  用于保证该字段有默认值
        比如性别
    primary key 主键 用于保证该字段的值具有唯一性、并且非空
        比如学号、员工编号等
    unique 唯一约束  用于保证该字段有唯一值,可以为空
        比如 座位号
    check 检查约束  mysql不支持
        比如年龄、性别
    foreign key 外键约束  用于限制昂个表的关系,用于保证该字段的值必须来自于主表的关联列的值
        在从表添加外键约束,用于引用主表中某列的值
    比如学生表的专业编号,员工表的部门编号,员工表的工种编号

    添加约束的时机
        1、创建表时
        2、修改表时
    
    约束的添加分类
        列级约束
            六大约束语法上都支持,但外键约束没有效果
        表级约束
            除了默认和非空 其他都支持
            
            
只支持: 默认 , 非空, 主键 , 唯一
*/ 
 
 
 
 -- create table 表名(
--     字段名 字段类型 约束
 
-- )
 
 # 创建表时添加约束
 -- 添加列级约束
 CREATE DATABASE students;
 USE students;
 
 CREATE TABLE studentinfo(
 id INT PRIMARY KEY,# 主键
 stuName VARCHAR(20) NOT NULL, #非空
 gender CHAR(1) CHECK(gender='' OR gender='') ,# chaeck 约束
 seat INT UNIQUE ,# 唯一约束
 age INT DEFAULT 18,#默认约束
 majorId INT FOREIGN KEY REFERENCES major(id) #外键约束
 );
 
 CREATE TABLE major(
 id INT PRIMARY KEY,
 majorName VARCHAR(20)
 );
 
DESC studentinfo;
 
 #查看表中所有的索引,包括主键 ,外键,唯一
 SHOW INDEX FROM studentinfo;
 
 
 
 # 添加表级约束
 /*
语法:
    [constraint 约束名] 约束类型(字段名)
 
 */
 
 DROP TABLE IF EXISTS EXISTS studentinfo;
 
 CREATE tabble studentinfo(
    d INT,
    stuName VARCHAR(20) ,
    gender CHAR(1) ,
    seat INT  ,
    age INT ,
    majorId INT ,
    CONSTRAINT pk PRIMARY KEY(id),
    CONSTRAINT uq UNIQUE(seat),
    CONSTRAINT ck CHECK(gender='',OR gender='')
    CONSTRAINT fk_studentinfo_major FOREIGN KEY(majorId) REFERENCES major(id) # 外键约束
 
 );
 
 # 通用写法
 CREATE TABLE IF EXISTS studentinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_studentinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    
);


/*
    外键
    1.要求在从表设置外键关系
    2.从表的外键列的类型和主表的关联列的列的类型要求一致或兼容,名称无要求
    3.主表的被关联列必须是一个key(主键或是唯一键)
    4.插入数据时,先插入主表,在插入从表
    5.删除数据时,先删除从表,再删除主表

*/
 
 
 
 
 #修改表时添加约束
 
 ALTER TABLE studentinfo
 MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
 
 #添加默认约束
 ALTER TABLE studentinfo MODIFY COLUMN age INT DEFAULT 80;
 
 
 #添加主键约束
 ALTER TABLE studentinfo MODIFY COLUMN id INT PRIMARY KEY;
 
 ALTER TABLE studentinfo ADD PRIMARY KEY(id);
 # 添加唯一键
 ALTER TABLE studentinfo MODIFY COLUMN seat INT UNIQUE;
 
 ALTER TABLE studentinfo ADD UNIQUE(seat);
 
 # 添加外键约束
 ALTER TABLE studentinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES majro(id);
 
 
 /*
 
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add [constraint 约束名] 约束类型 (字段名) [外键的引用]
 
 */
 
 
 
 /*
 修改表时删除约束
 
 */

# 删除非空约束
ALTER TABLE studentinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#删除主键约束
ALTER TABLE studentinfo DROP PRIMARY KEY;

# 删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

SHOW INDEX FROM stuinfo; 

# 删除外键约束
ALTER TABLE studentinfo DROP FOREIGN KEY fk_stuinfo_major ;
 
 
 
 
 # 向表emp2的id列中添加primary key 约束 (my_emp_id_pk)
 
 ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
 ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY (id);
 # 向表dept2的id列中添加primary key 约束(my_dept_id_pk)
ALTER TABLE 

# 向表emp2中添加列dept_id,并在其中定义foreign key 约束,与之相关联的列是dept2表中的id列 
 
 
 ALTER TABLE emp2 ADD COLUMN dept_id INT 
 
 ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
 
 
 
 
 
 
 # 标识列
 
 /*
 
又称自增列
含义:
    可以不用手动插入值,系统提供默认的序列值
    
标识列需要是一个key
一个表中只能有一个标识列
标识列的类型只能是数值型

标识列可以通过set auto_increment_increment=3;设置步长
可以通过手动插入值,设置起始值
 */
 
 CREATE TABLE tab_identity(
    id INT PRIMARY KEY AUTO_INCREMENT ; # 自动增长
    NAME VARCHAR(20)
 );
 
 # 查看相关变量
  SHOW VARIABLES LIKE '%auto_increment%';

 # 设置步长
 SET auto_increment_increment=3;
 
 
 # 修改表时 设置标识列
 
 ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
 
  # 修改表时 设置标识列
 
  ALTER TABLE tab_identity MODIFY COLUMN id INT ;

 
 
 命令行不显示中文,显示为???输入该命令
 set names gbk;
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

posted @ 2021-01-08 16:42  梁永旺  阅读(41)  评论(0编辑  收藏  举报