数据库系列-mysql-sql执行示例

数据库定义语言

-- 数据库查询语句模板
 
-- !数据库创建
-- 创建数据库test，如果存在则不创建，默认字符集为utf8，不区分大小写，cs为大小写敏感，也可以使用utf8_bin，字符采用二进制存储
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
-- 数据库删除
DROP DATABASE IF EXISTS `test`;
 
-- !数据库切换
USE `test`;
 
-- !数据库列表
SHOW DATABASES;
 
-- !数据库表列表
SHOW TABLES;
 
-- !创建表
-- 创建表tb_test，comment 为描述 primary key 为主键 index为索引 unique为唯一索引
CREATE TABLE `tb_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- 创建外键表
CREATE TABLE `tb_test_fk` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- 创建数据表，添加外键
CREATE TABLE `tb_test_fk` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
  FOREIGN KEY (`name`) REFERENCES `tb_test` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- 添加外键
  ALTER TABLE `tb_test_fk` ADD CONSTRAINT `fk_test_fk` FOREIGN KEY (`id`) REFERENCES `tb_test` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
-- 创建视图
CREATE VIEW `vw_test` AS SELECT * FROM `tb_test` left join `tb_test_fk` on `tb_test`.`name` = `tb_test_fk`.`name`;
-- !修改表结构
-- 添加字段email,默认为最后一列，first为第一列，after为指定列之后
ALTER TABLE `tb_test` ADD `email` varchar(255) NOT NULL COMMENT '邮箱';
-- 修改字段名称、类型和约束
ALTER TABLE `tb_test` CHANGE `name` `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称';
-- 修改字段类型和约束
ALTER TABLE `tb_test` MODIFY `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称';
-- 删除字段
ALTER TABLE `tb_test` DROP `name`;
-- 删除外键
ALTER TABLE `tb_test` DROP FOREIGN KEY `fk_test_fk`;

数据库操作语言

-- !添加数据
-- 添加单行数据
INSERT INTO `tb_test` (`name`, `age`) VALUES ('张三', 20);
-- 添加多行数据
INSERT INTO `tb_test` (`name`, `age`) VALUES ('张三', 20), ('李四', 21);
 
-- !更新数据
-- 更新单个字段
UPDATE `tb_test` SET `name` = '张三' WHERE `id` = 1;
-- 更新多个字段
UPDATE `tb_test` SET `name` = '张三', `age` = 20 WHERE `id` = 1;
 
-- !删除数据
DELETE FROM `tb_test` WHERE `id` = 1;

数据库查询语言

-- 查询数据
-- !查询单条数据
USE employees;
-- !查询一张数据表中的所有数据，生产中不建议使用
SELECT * FROM employees;
-- !分页查询 前面是起始位置，后面是查询行数
SELECT * FROM employees LIMIT 20,10;
-- !排序 desc 倒序 asc 正序
SELECT * FROM salaries order by salary;
-- !查询指定字段
SELECT first_name, last_name FROM employees;
-- !指定条件查询
SELECT * FROM salaries WHERE salary > 150000;
-- 去重
SELECT distinct emp_no FROM salaries WHERE salary > 150000;
-- 分组查询
-- 查询前100行数据，每个部门里面有哪些员工
select dept_no,group_concat(emp_no) from current_dept_emp group by dept_no limit 100;
-- 查询当前数据表有多少条数据
SELECT count(*) FROM employees;
-- 查询员工表中1959年出生的员工
-- 模糊查询，生产中不建议使用
SELECT * FROM employees WHERE birth_date LIKE '1959%';
-- left
select * from employees where left(birth_date,4) = '1959';
-- 时间比较
select * from employees where birth_date >= '1959-01-01' and birth_date <= '1959-12-31';
 
-- sql 优化
-- 1、查询表数据的时候，使用分页limit
-- 2、查询数据时，即便你要查询所有的列，也要指定列名
select * from employees; -- select l1,l2,l3 from employees;
-- 3、不要使用模糊查询
-- 4、不要使用反向查询
-- select * from employees where gender != 'F';
-- 5、能使用视图就不要去使用多表连接查询，子查询。
-- 6、做查询计划，检验sql语句
 
-- 多表查询
-- 两个表连查
select t.id as tid,s.id as sid,t.name,s.name from teacher t,student s where t.id = s.tid;
-- 内连接 inner 外连接 left right
select * from teacher t right join student s on t.id = s.tid;

数据库控制语言

创建账号
CREATE USER '你的用户名'@'localhost' IDENTIFIED BY '你的密码';
#把刚刚创建的数据库的管理权限给予刚刚创建的MySQL用户
GRANT ALL PRIVILEGES ON 你的数据库名.* TO '你的用户名'@'localhost';
#刷新权限，使用设置生效
FLUSH PRIVILEGES;
# 取消权限
revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址'    -