-- ALTER TABLE student MODIFY age VARCHAR(10) NOT NULL;
-- ALTER TABLE student MODIFY age INT(2);
-- 唯一约束
CREATE TABLE tset(
id int,
phone VARCHAR(20) UNIQUE
);
ALTER TABLE student MODIFY `name`VARCHAR(10) UNIQUE;
-- 6.3删除唯一约束
ALTER TABLE student DROP INDEX `name`;
-- 7主键约数
CREATE TABLE tset02(
id int(11) PRIMARY key ,
username VARCHAR(12)
);
-- 8创建完表添加主键
ALTER TABLE tset MODIFY id int PRIMARY key;
-- 8.2删除主键约束
ALTER TABLE tset drop PRIMARY key;
-- 9自动增长
CREATE TABLE test03 (
id int PRIMARY key auto_increment,
username VARCHAR(20)
);
-- 9.1删除自动增长
ALTER TABLE test03 MODIFY id int ;
-- 9.2 创建表之后添加自动增长
ALTER TABLE test03 MODIFY id int auto_increment;
SELECT DATABASE();
-- 10查询数据库中所有表
SHOW TABLES;
-- 11查询表结构
DESC student;
-- 12修改表名
ALTER TABLE test03 RENAME to test003;
-- 12.2修改表的字符集
ALTER TABLE test003 CHARACTER SET utf8;
-- 13添加一列
ALTER TABLE test003 add id2 int(11);
-- 13.2修改列名
ALTER TABLE test003 CHANGE id2 id3 VARCHAR(11);
-- 14删除列
ALTER TABLE test003 DROP id3;
-- DQL排序查询
-- 默认升序 空值在前面
SELECT *FROM student ORDER BY brithday;
-- 降序
SELECT *FROM student ORDER BY brithday DESC;
-- 2聚合函数
-- count
SELECT COUNT(name)FROM student;
SELECT COUNT(age) FROM student;
SELECT COUNT(*) FROM student;
-- max min
SELECT MAX(age) FROM student;
SELECT MAX(score) FROM student;
SELECT MIN(age)FROM student;
-- sum
SELECT SUM(age)FROM student;
SELECT SUM(score)FROM student;
-- avg
SELECT AVG(age)FROM student;
SELECT AVG(score)FROM student;
-- 分组
-- 按性别分组计算平均值
SELECT sex,AVG(score) FROM student GROUP BY sex;
SELECT AVG(score) FROM student GROUP BY sex;
-- 按性别分组计算平均值,人数
SELECT sex,AVG(score),COUNT(*) FROM student GROUP BY sex;
SELECT sex as 性别,AVG(score) AS 平均分,COUNT(*)AS 人数 FROM student GROUP BY sex;
-- 按性别分组计算平均值,人数 ,低于七十分不参与分组
SELECT sex as 性别,AVG(score) AS 平均分,COUNT(*)AS 人数 FROM student WHERE score>70 GROUP BY sex;
-- 按性别分组计算平均值,人数 ,低于七十分不参与分组,分组后人数大于2
SELECT sex as 性别,AVG(score) AS 平均分,COUNT(*)AS 人数 FROM student WHERE score>70 GROUP BY sex HAVING COUNT(*)>2;
-- 插入
INSERT INTO student (id,name,score) VALUES(10,"不知道","88.0");
INSERT INTO student VALUES(11,"知道","21","87.0","2020-04-03","2020-04-03","男");
-- 外键约束
-- 一对多建表案例
-- 1创建多对多表关系
CREATE TABLE tb_user(
uid int PRIMARY key auto_increment,
user_name VARCHAR(10),
pwd VARCHAR(20),
namea VARCHAR(20),
brithday date,
sex CHAR DEFAULT '男',
telepoone VARCHAR(11),
email VARCHAR(20)
);
-- 收藏表
CREATE TABLE tab_favort(
rid int PRIMARY key auto_increment,
date datetime,
uid int,
--联合主键
PRIMARY key (uid,rid),
FOREIGN key (rid) REFERENCES tab_route(rid),
FOREIGN key (uid) REFERENCES tab_user(uid)
);
CREATE TABLE tab_category(
id int PRIMARY key auto_increment,
cname VARCHAR(30)
);
CREATE table tab_route(
rid int PRIMARY key auto_increment,
rname VARCHAR(50),
price int ,
rdate date,
cid int ,
FOREIGN key (cid) REFERENCES tab_category(id)
);
-- DML
-- 添加数据
INSERT INTO student(id,name,score) VALUES(13,"添加新数据","99.0");
INSERT INTO student VALUES(11,"知道","21","87.0","2020-04-03","2020-04-03","男");
-- 删除数据
DELETE FROM `student` WHERE id=13;
-- 删除整张表
DELETE FROM `student` ;
-- 修改数据
UPDATE student SET id=14, age=19, name="王八" WHERE id=11;