数据库学习2——sql常用指令

1 数据定义语言DDL（Data Define Language）

1.1 数据库操作

操作
命令
创建数据库 格式：create database [if not exists] 数据库名;
示例：drop database if not exists mydb1;
删除数据库 格式：create database [if exists] 数据库名;
示例：drop database if exists mydb1;
修改数据库编码 alter database mydb1 character set utf8;
alter database mydb1 character set utf8mb4;
注：（1）utf8mb4是比utf8更大的字符集，能兼容四字节unicode；
       （2）在 MySQL 中所有的 UTF-8 编码都不能使用“-”符号。
查看数据库列表 show databases;
使用数据库 use 数据库名;
示例：use mydb1;
导出数据库 格式：mysqldump [选项] [--databases] 数据库名 [选项 表名] > 脚本名;
示例:（1）导出所有数据库：mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases > D:\mysql\sql\testdb.sql
       （2）导出指定数据库：mysqldump -uroot -p --host=127.0.0.1 --port=3306 --databases mydb1 > D:\mysql\sql\testdb.sql
       （3）只导出表结构：mysqldump -uroot -p --no-data --databases mydb1 > D:\mysql\sql\testdb.sql
       （4）导出指定表：mysqldump -uroot -p mydb1 --tables mytable1 > D:\mysql\sql\testdb.sql
注：上述指令需要在未登录mysql时使用，末尾不得有分号。
参考：mysqldump使用方法(MySQL数据库的备份与恢复)
导入数据库 非登陆导入：mysql -u root -p newdb < D:\mysql\sql\testdb.sql
登录导入：source D:\mysql\sql\testdb.sql
注：导入指令都不可以带分号
查看数据库存储位置 show variables like `%datadir%`;

1.2 数据表操作

操作
命令
创建表 格式：create table 表名(列名 列类型, 列名 列类型, ...);
示例：
create table mytable1(
    id int auto_increment primary_key,
    name varchar(10) not null comment '名字',
    age int default 0,
    gender char(5)
);
说明：id自增长且作为主键，name不能为null且字段注释为名字，age指定默认值为0。
查看表结构 格式：desc 表名;
查看创表信息
（注释、字符集）		 格式：show create table 表名;
示例：show create table mytable1;
修改表名 格式：alter table 表名 rename to 新表名;
示例：alter table mytable1 rename to newtable;
删除表 格式：drop table [if exists] 表名;
修改表的字符集 格式：alter table 表名 character set 字符集;
示例：alter table mytable1 character set gbk;
插入列 格式：alter table 表名 add 列名 数据类型;
示例：alter mytable1 add score int(3);
修改列类型 格式：alter table 表名 modify 列名 列的类型( 列的约束);
示例：alter table mytable1 modify score varchar(4);
修改列名 示例：alter table 表名 change 列名 新的列名 列的类型( 列的约束);
示例：alter table mytable1 change gender sex varchar(5);
删除列 格式：alter table 表名 drop 列名;
示例：alter table mytable1 drop score;

1.3 MySql数据类型
整型 TINYINT(1byte), SMALLINT(2), MEDIUMINT(3), INT/INTEGER(4), BIGINT(8)
浮点型 FLOAT(4byte), DOUBLE(8),
float(size,d)/double(size,d): size指定字节数，d指定小数位数
定点数类型 DECIMAL(M, D) 表示最多M位，其中有D位小数，存储字节M+2bytes
位类型 BIT(M) M位二进制位数
日期和时间 YEAR, DATE(yyyymmdd), TIME(fsp), DATETIME(fsp), TIMESTAMP(fsp)
可指定日期和时间格式,fsp: 指定小数秒精度
字符串 CHAR(0-255bytes)、VARCHAR(0-65535bytes)、TINYTEXT(0-255bytes)、TEXT(0-65535bytes)、MEDIUMTEXT(0-224-1bytes)、LONGTEXT(0-232-1bytes)
二进制类型 BLOB(0-65535bytes)、LONGBLOB(0-232-1bytes)
枚举类型 ENUM('春','夏','秋','冬')，ENUM类型取值范围需要在定义字段时指定
其他类型 SET, BINARY, VARBINARY, BLOG, JSON, 空间类型

类型属性
说明
UNSIGNED UNSIGNED表示无符号类型（非负）
ZEROFILL 配合类型指定的精度M使用，指定ZEROFILL表示不够M位时，用0在左边填充

2 数据操作语言DML（Data Manipulation Language）

操作
命令
插入数据 insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
insert into 表名 values(值1,值2,...值n);
insert into 表名(列名1,列名2) values(值1,值2);
示例：insert into mytable1 values(001, "Li Si", 24 );
insert into mytable1(id, name) values(001, "Li Si");
修改数据 修改所有行：update 表名 set 列名 = 值;
只修改一行：update 表名 set 列名 = 值 where 列名=值;
示例：update mytable1 set age=27 where name='Li Si';
删除数据 删除一行数据：delete from 表名 where 列名 = 值;
删除表中所有数据：delete from 表名;
truncate table 表名;（高效删除，先删除表再创建新表）

3 数据控制语言DCL（Data Control Language）

操作
命令
添加用户 格式：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
删除用户 格式：DROP USER '用户名'@'主机名';
查询权限 格式：SHOW GRANTS FOR '用户名'@'主机名';
例：SHOW GRANTS FOR 'lisi'@'%';
授予权限 格式：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
示例：GRANT ALL ON . TO 'zhangsan'@'localhost';
撤销权限 格式：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
示例：REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

4 数据查询语言DQL(Data Query Language)

操作
命令
范围内查询 （1）区间内：[between]...and, &&
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
（2）集合内：or, in()
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
SELECT * FROM student WHERE age NOT IN (22,18,25);
空值判断 SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;
模糊查询 _: 表示单个任意字符
%: 表示多个任意字符
示例：（1）查询姓马的人: SELECT * FROM student WHERE NAME LIKE '马%';
          （2） 查询姓名第二个字是化的人: SELECT * FROM student WHERE NAME LIKE "_化%";
去除重复值 格式：SELECT DISTINCT 列名称 FROM 表名称
示例：SELECT DISTINCT NAME FROM student;
排序查询 格式：select * from 表名 order by 列1 asc
聚合函数 示例：（1）计算和、平均值、数量
SELECT sex ,SUM(math), AVG(math),COUNT(id) FROM student;
（2）计算最小值、最大值
SELECT sex , MIN(math), MAX(math) FROM student;
分组查询 根据指定列分组进行查询
示例：SELECT sex , AVG(math) FROM student GROUP BY sex;
分页查询 格式：select * from 表名 limit [页索引,] 每页显示条数
示例：SELECT * FROM student LIMIT 0,3; -- 第1页，查询三条
内连接查询 用于查询两个表的交集。
隐式内连接：
示例：SELECT emp.name, emp.gender, dept.name FROM emp, dept WHERE emp.`dept_id` = dept.`id`;
显式内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件;
示例：SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
外连接查询 （1）左外连接：查询的是左表所有数据以及其交集部分。
格式：select 字段列表 from 表1 left [outer] join 表2 on 条件;
示例：SELECT t1.*, t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
（2）右外连接：查询的是右表所有数据以及其交集部分。
格式：select 字段列表 from 表1 right [outer] join 表2 on 条件;
示例：SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
嵌套查询 子查询可以作为条件和虚拟表。
示例：SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
posted @ 2024-06-01 16:37  wrj的博客  阅读(4)  评论(0)    收藏  举报