SQL基础练习

-- 创建数据库
CREATE DATABASE 10_8;
-- 查看当前数据库
SELECT DATABASE();
-- 使用数据库
USE 10_8;
-- 建表a
CREATE TABLE a(
  id INT,
  sname VARCHAR(20),
  birthday DATE,
  gender VARCHAR(5),
  school VARCHAR(20)
);
-- 插入一列
ALTER TABLE a ADD image BLOB;
-- 设置主键
ALTER TABLE a ADD CONSTRAINT PRIMARY KEY(id);
-- 修改表名为form1
RENAME TABLE a TO form1;
-- 查看表创建细节
SHOW CREATE TABLE form1;
-- 修改列名sname 为 name
ALTER TABLE form1 CHANGE sname NAME VARCHAR(20);
-- 插入一列
ALTER TABLE form1 ADD addr TIMESTAMP;
-- 修改地址的类型
ALTER TABLE form1 MODIFY addr VARCHAR(50);
-- 查看表的字段信息
DESC form1;
-- 批量插入数据
INSERT INTO form1 VALUES
(1,'张三','1996-5-1','男','通信与信息工程学院','shuai','西安'),
(2,'张三丰','1996-5-2','女','通信与信息工程学院','liang','西安'),
(3,'李四','1996-5-4','男','电子工程学院','shuai','西安'),
(4,'狗蛋','1996-5-3','女','通信与信息工程学院','shuai','西安');
--  让狗蛋变成日本人吧！
UPDATE form1 SET addr='日本' WHERE NAME='狗蛋';
-- 让通院的男生更帅吧！
UPDATE form1 SET image='supercool' WHERE school='通信与信息工程学院' AND gender='男';
-- 查询张姓学生
SELECT * FROM form1 WHERE NAME LIKE '张%';
-- 看一下都有那些image，不看重复的
SELECT DISTINCT image FROM form1;
-- 按id倒序查询
SELECT * FROM form1 ORDER BY id DESC;
-- 分组查询
SELECT gender,COUNT(*) SUM FROM form1 GROUP BY gender;
-- 查询当前表的键
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='form1';
-- 查询记录数，结果重命名为sum
SELECT COUNT(*) AS SUM FROM form1;
-- 查询生命周期显示
SHOW FULL PROCESSLIST;
-- 创建另一张表
USE 10_8;
CREATE TABLE form2(
id INT,
math DOUBLE,
chinese DOUBLE,
english DOUBLE,
TIMESTAMP TIMESTAMP
);
-- 添加主键
ALTER TABLE form2 ADD PRIMARY KEY(id);
ALTER TABLE form2 MODIFY id INT AUTO_INCREMENT;
DESC form1;
INSERT INTO form2(math,chinese,english) VALUES
(77,88,99),
(44,55,66),
(11,22,33);

INSERT INTO form2(math,chinese,english) VALUES(92,95,98);
DELETE FROM form2 WHERE id=4;
-- 内连接查询
SELECT * FROM form1 f1 INNER JOIN form2 f2 ON f1.id = f2.id;
-- 左连接查询
SELECT * FROM form1 f1 LEFT JOIN form2 f2 ON f1.id = f2.id;
-- 右连接查询
SELECT * FROM form1 f1 RIGHT JOIN form2 f2 ON f1.id = f2.id;
-- 添加主外键
ALTER TABLE form2 ADD CONSTRAINT fk_form1_form2 FOREIGN KEY(id) REFERENCES form1(id);

ALTER TABLE form2 ADD CONSTRAINT fk_from1_form2 FOREIGN KEY(id) REFERENCES form1(id) ON DELETE CASCADE;

-- 自然连接
SELECT * FROM form1 NATURAL JOIN form2;
SELECT * FROM form1 NATURAL LEFT JOIN form2;
SELECT * FROM form1 NATURAL RIGHT JOIN form2;