#登录和退出数据库
mysql -u root -p    #输入密码 -P指定端口，-D指定数据库 ，--prompt 修改提示符
exit      #quit，\q
mysql -u root -p
SELECT VERSION();
SELECT NOW();
SHOW ENGINES;
SHOW VARIABLES LIKE 'have%';  #查看存储引擎信息
SHOW VARIABLES LIKE 'storage_engine';  #查看默认存储引擎
SHOW WARNINGS \G;

#数据库创建，删除，进入数据库
SHOW DATABASES;
DROP DATABASE IF EXISTS `testdatabase`;
CREATE DATABASE IF NOT EXISTS `testdatabase` DEFAULT CHARACTER SET = 'utf8';
ALTER DATABASE testdatabase CHARACTER SET = 'utf8';
SHOW CREATE DATABASE `testdatabase`;
USE testdatabase;   #这里不应该使用 `XX` 括住名称
SELECT USER();
SELECT DATABASE();
SHOW TABLES;  #注释符号
help 'SELECT';
help 'INT';
help '+';


#表的创建和删除，创建表同时设置属性
CREATE TABLE IF NOT EXISTS testestable(
id INT ZEROFILL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE, #检索效率 CHAR>VARCHAR>TEXT
    age TINYINT UNSIGNED NOT NULL,
brithday DATE,  #1000-01-01~9999-12-31 3个字节
    sex TINYINT(1) DEFAULT 0 COMMENT '0代表男,1代表女',
address TINYTEXT,
married ENUM('是','否','保密'),   #最多65535个值
    fav_food SET('apple','orange','fish','meat','cake') DEFAULT NULL,   #最多64个成员
    score DECIMAL(2,2),
CHECK(score > 0)
    ) ENGINE = 'InnoDB' CHARACTER SET = 'utf8';
# InnoDB(支持事务、崩溃修复、并发控制，适合频繁更新删除)
# MyISAM(适合对数据的存储和读取，空间使用比较低)、
# MEMORY(速度快、安全性差)
SHOW CREATE TABLE testestable \G;
DESC testestable;
SHOW FULL COLUMNS FROM testestable \G;
SHOW TABLES;
DROP TABLE testestable;




#key键：
  #主键：唯一标识一条记录，不能有重复的，不允许为空,用来保证数据完整性
CREATE TABLE IF NOT EXISTS test1(
username CHAR(20),
password CHAR(20),
PRIMARY KEY(username)
);

#外键：外键是另一表的主键, 一个表可以有多个外键, 可以是空值,用来和其他表建立联系用的
  #外键连接必须连接的是主表的主键，如果连接主表主键的一部分，必须连接主键的第一个字段
  #外键关联的主从表字段数据类型必须完全一样
CREATE TABLE IF NOT EXISTS test2(
id INT PRIMARY KEY AUTO_INCREMENT,
username CHAR(20),
level TINYINT UNSIGNED
    #CONSTRAINT uname FOREIGN KEY(username) REFERENCES test1(username)
);


#修改表：
    #修改表名
ALTER TABLE test1 RENAME temp;
SHOW  TABLES;
DESC temp;
ALTER TABLE temp RENAME TO test1;


#修改表属性数据类型
DESC test1;
ALTER TABLE test1 MODIFY password VARCHAR(50);
DESC test1;
INSERT  test1 VALUES('FreezeJ','123456');
SELECT * FROM test1;
#ALTER TABLE test1 MODIFY username int;  #有值的情况下转换字段类型失败，因为字符串类型无法自动转型为整数类型
SHOW WARNINGS LIMIT 1;  #查看第一条错误
DELETE FROM test1;  #删除表中所有元素
SELECT * FROM test1;
ALTER TABLE test1 MODIFY username int;
DESC test1;
ALTER TABLE test1 MODIFY username CHAR(10);

#修改表属性
ALTER TABLE test1 CHANGE password passwd CHAR(20);

#增加字段
DESC test1;
ALTER TABLE test1 ADD sex TINYINT(1) NOT NULL after passwd;

#删除字段
ALTER TABLE test1 DROP sex;  #删除字段会直接删除，不管有没有内容，也不会要求确认

  #修改属性排列顺序
ALTER TABLE test1 MODIFY username CHAR(10) AFTER passwd;  #会修改username的类型
ALTER TABLE test1 MODIFY username CHAR(10) FIRST;


#索引：
  #创建带索引的数据库表
CREATE TABLE IF NOT EXISTS test3(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
UNIQUE INDEX index_id(id)
);    #唯一索引，唯一索引指的是被索引的字段值唯一
SHOW CREATE TABLE test3 \G;


#在已存在表上添加索引
#create [unique|fulltext|spatial] index indexname on tablename (columnname [(length)] [ASC|DESC]);
    #Alter语句添加索引
    #Alter table tablename add [unique|fulltext|spatial] index indexname (columename [(length)] [ASC|DESC]);
CREATE INDEX index_username ON test1(username);
ALTER TABLE test1 ADD INDEX index_passwd(passwd);
#查看索引
SHOW INDEX FROM test1 \G;

#删除索引
DROP INDEX index_username ON test1;
ALTER TABLE test1 DROP INDEX index_passwd;



CREATE TABLE student(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name CHAR(20) NOT NULL,
num INTEGER UNSIGNED UNIQUE NOT NULL,
age TINYINT UNSIGNED NOT NULL,
sex TINYINT(1) DEFAULT 0,
address CHAR(50),
room CHAR(5),
class TINYINT
);

CREATE TABLE teacher(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name CHAR(20) NOT NULL,
sex TINYINT(1) DEFAULT 1 COMMENT '1代表女',
teach_class TINYINT DEFAULT NULL
);




#插入
INSERT INTO student VALUES(1,'DRJ', 11, 21, 0, 'AAAA', '6-208', 1);
INSERT INTO student VALUES(2,'LYC', 10, 20, 0, 'AAAA', '6-210', 2);
INSERT INTO student VALUES(3,'LZY', 25, 23, 0, 'BBBB', '6-208', 3);
INSERT INTO student VALUES(4,'WJX', 5, 21, 0, 'CCCC', '6-207', 1);
INSERT INTO student VALUES(5,'YJH', 29, 21, 0, 'DDDD', '6-208', 1);
INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);

INSERT INTO teacher VALUES(1,'YYY', 1, 2);
INSERT INTO teacher VALUES(2,'QQQ', 2, 3);
INSERT INTO teacher VALUES(3,'WWW', 1, 1);
INSERT INTO teacher VALUES(4,'EEE', 2, 3);
INSERT INTO teacher VALUES(5,'RRR', 1, 4);

#更改属性
ALTER TABLE student MODIFY sex TINYINT(1) COMMENT '0代表男';

#更新属性
UPDATE student SET name = 'ZXB', num = 27 WHERE id = 4;

#删除属性
DELETE FROM student WHERE name = 'WL';
INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);


#常用查询语句

SELECT name, address FROM student WHERE room LIKE '__208' ORDER BY age DESC;
SELECT name, room FROM student GROUP BY room;
SELECT name FROM student WHERE age = (SELECT age FROM student ORDER BY age DESC LIMIT 1);
SELECT name AS '宿舍成员' FROM student GROUP BY room;
SELECT DISTINCT age AS '年龄分布' FROM student ORDER BY age;

SELECT student.name AS '学生名字',student.class AS '班级', teacher.name AS '老师姓名'
FROM student JOIN teacher
ON student.class = teacher.teach_class;

SELECT name FROM student
UNION     #UNION自动去重复，UNION ALL可以不去重复
SELECT name FROM teacher;

#聚集函数
#AVG() - 返回平均值
#COUNT() - 返回行数
#FIRST() - 返回第一个记录的值
#LAST() - 返回最后一个记录的值
#MAX() - 返回最大值
#MIN() - 返回最小值
#SUM() - 返回总和
SELECT count(*) AS '总人数' FROM student;

CREATE VIEW view_room AS
SELECT name, room
FROM student
WHERE room = '6-208';

SHOW  TABLES;
SELECT * FROM view_room;
INSERT INTO student VALUES (7,'WJZ', 17, 21, 0, 'GGGG', '6-208', 5);
SELECT * FROM view_room;