常用指令

DDL(数据库定义语言)

  1. 库和表的字符集无关联，可单独设置
  2. 表类型影响表性能，MyIsam性能大但功能少
  3. 外键约束：
    1. CASCADE：父表更新或删除时，自动更新或删除子表外键列
    2. SET NULL：父表更新或删除时，子表外键列为空
    3. RESTRICT/NO ACTION：拒绝对附表进行更新或删除操作 ```sql CREATE DATABASE IF NOT EXISTS db DEFAULT CHARACTER SET utf8mb4; # 建库 USE db; # 打开数据库 DROP DATABASE IF EXISTS db DEFAULT; # 删库 DROP TABLE tb; # 删表

      修改数据库信息

      ALTER DATABASE db CHARACTER SET gbk; ALTER DATABASE db CHARSET utf8 COLLATE utf8mb4_bin;

SHOW DATABASES; # 查看数据库 SHOW CHARSET; # 查看可用字符集 SHOW COLLATION; # 查看可用字符排序校对规则 SHOW CREATE DATABASE db; # 查看详细命令 SHOW WARNINGS; # 查看警告

查看表

SHOW TABLES FROM tb; SHOW COLUMNS FROM tb; # 查看表结构 SHOW INDEXES FROM tb; # 查看表索引

CREATE TABLE IF NOT EXISTS tb ( id TINYINT AUTO_INCREMENT, # 自增 username VARCHAR(16) UNIQUE KEY, # 唯一 password CHAR(32) NOT NULL, # 非空 age INT UNSIGNED ZEROFILL, # 非负，填零 gender ENUM(‘0’, ‘1’, ‘-1’) DEFAULT ‘0’, wealth DECIMAL(8, 2) NULL, # 可空

  1. rid TINYINT, # 外键列和参照列类型要一致
  2. FOREIGN KEY (rid) REFERENCES roles (id) ON DELETE CASCADE # 外键，父表与子表联动删除
  3.     PRIMARY KEY(id, username) # 一张表有且仅有一个主键，但可以是联合主键

) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4; # 建表

ALTER TABLE tb ADD income DECIMAL(5, 2) UNSIGNED DEFAULT 0 NOT NULL after id; # 添加字段 ALTER TABLE tb ADD COLUMN adult ENUM(‘0’, ‘1’) AFTER cid; # 添加列 ALTER TABLE tb ADD CONSTRAINT UNIQUE index_name(rid, id); # 对联合索引添加约束 ALTER TABLE tb DROP FOREIGN KEY rid; # 删除外键 ALTER TABLE tb DROP COLUMN adult; # 删除列 ALTER TABLE tb DROP username; # 删除字段 ALTER TABLE tb MODIFY COLUMN age INT TINYINT FIRST rid; # 改列定义 ALTER TABLE tb MODIFY username VARCHAR(32); # 修改字段属性 ALTER TABLE tb ALTER COLUMN gender DROP DEFAULT; # 删除默认约束 ALTER TABLE tb RENAME newtb; # 改表名 ALTER TABLE tb CHANGE COLUMN username name char(16); # 改列名 ALTER TABLE tb CHANGE id id INT UNIQUE KEY COMMENT ‘index’; # 修改字段 ALTER TABLE tb charset = gbk; # 修改字符集

  1. <a name="XKoYV"></a>
  2. ### DQL(数据库查询语言)
  3. ```sql
  4. SELECT VERSION(); # 查看服务器版本
  5. SELECT USER(); # 查看当前用户
  6. SELECT NOW(); # 显示当前时间
  7. SELECT DATABASE(); # 查看当前数据库
  9. SELECT t.* FROM tb AS t WHERE t.gender='other' GROUP BY t.gender ASC HAVING COUNT(t.age)>=10; # 分组条件升序筛查
  10. SELECT * FROM tb ORDER BY age DESC LIMIT 5,3; # 排序降序限条查询

DML(数据库操纵语言)

  1. INSERT INTO tb (name, password, wealth) VALUES ('用户名', '密码', '20.00'); # 插入数据
  2. INSERT INTO tb SET password=md5(123456);
  3. INSERT INTO tb SELECT * FROM country;
  5. DELETE FROM tb WHERE id=4; # 删除数据
  7. UPDATE tb SET age=age+5; # 更新数据
  8. UPDATE A INNER JOIN B ON A.a=B.a SET A.a=B.b; # 内连接

其他

  1. PROMPT [\D][\u@\h][\d]; # 提示符修改为[完整日期][用户名@主机名][数据库名]
  3. DESC tb; # 查看表结构
  4. RENAME TABLE newtb TO tb; # 改表名
  6. SET sql_mode='TRADITIONAL' # 严格模式，对超出范围的值进行报错

数据类型与比较运算符

数据类型.png

比较运算符 ANY SOME ALL
>、>= Min Min Max
<、<= Max Max Min
= 任意 任意
<>、!=、<=> 任意
  1. =ANY与IN等效
  2. !=ALL、<>ALL与NOT IN等效

    高级用法

    ```sql source demo.sql; — 导入SQL文件

— 高级查询 select distinct name as ‘姓名’, gender as ‘性别’ from tb where age between 18 and 20 group by country having gender = ‘male’ order by name desc limit 3; select from tb where name like ‘%三‘; — %为任意个数任意字符，为任一字符，如不使用通配符则like等同于= select from tb where name in(‘张三’, ‘李四’); select from tb where country is not null; select from tb limit 40, 10; — 翻页功能实现：limit (n-1) * pageSize, pageSize

— 高级插入 insert ignore into new_tb (id, name, age) select id, name, age from tb; insert into tb set name=’赵六’, gender=3, country=5; insert into tb (id, name, gender) values (3, ‘小红’, ‘female’) on duplicate key update name = ‘小红’; replace ignore into new_tb (id, name, age) select id, name, age from tb;

— 高级删除 truncate tb; — 清空并初始化表

— 联合查询，同列数纵向堆叠 select id, col_1 as A, col_2 as B from tb_A union all select id, col_A as A, col_B as B from tb_B;

— 连接查询，横向对接 select tb.name, tb.gender, new_tb.new_sexual from tb cross join new_tb; select tb.name, tb.gender, new_tb.new_sexual from tb inner join new_tb on tb.id = new_tb.id; select A.name, A.gender, B.new_sexual from tb as A left outer join new_tb as B on A.id = B.id; select tb.name, tb.gender, new_tb.new_sexual from tb right outer join new_tb on tb.id = new_tb.id;

— 子查询 select from tb where row(name, gender) = ( select name, gender from tb where id = all( select id from tb where name = ‘张三’ ) ); select from tb where exists( select * from new_tb where name like ‘%四’ and tb.id = new_tb.id );

  1. <a name="08b55fea"></a>
  2. ## 管理
  3. ```sql
  4. -- 数据管理
  5. mysqldump -hlocalhost -uroot -p db > backup.sql -- 备份
  6. mysql -hlocalhost -uroot -p db < backup.sql -- 还原
  8. -- 用户管理
  9. use mysql;
  11. select * from user; -- 查看用户
  12. create user 'user'@'localhost' identified by 'passwd'; -- 创建用户
  13. drop user 'user'@'localhost'; -- 删除用户
  14. set password for 'user'@'localhost' = password('passwd'); -- 设置密码
  16. -- 权限管理 DCL(数据库控制语言)
  17. grant delete, insert, update on *.* to 'user'@'localhost'; -- 设置权限
  18. revoke select, create on *.* from 'user'@'%'; -- 取消权限
  19. flush privileges; -- 刷新权限