登录数据库
mysql -h 127.0.0.1 -p 3306 -u root -pxxx
查看当前数据库的连接
show processlist
导出数据库
mysqldump -h 127.0.0.1 -p 3306 -u root -pxxx 数据库名 > dbname.sql
还可以进一步导出表
mysqldump -h 127.0.0.1 -p 3306 -u root -pxxx 数据库名 表名 > dbname-tablename.sql
导入数据库
mysql -h 127.0.0.1 -p 3306 -uxxx -pxxx < dbname.sql
shell 中执行sql语句
mysql -uxxx -pxxx -e "show databases;"
DDL语句 和 DML语句
- DDL（Data Definition Languages）语句
数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等 - DML（Data Manipulation Language）语句
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）
查看当前服务器支持的存储引擎
mysql > SHOW ENGINES;
创建数据库
CREATE DATABASE xxx
CREATE DATABASE IF NOT EXISTS xxx
删除数据库
DROP DATABASE xxx
DROP DATABASE IS EXISTS xxx
创建表
CREATE TABLE IF NOT EXISTS t_course (
course_id int AUTO_INCREMENT,
course_name varchar(255) NOT NULL,
course_teachser_ID int NOT NULL,
... NOT NULL DEFAULT 1,
......
primary key (course_id, course_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
AUTO_INCREMENT
自增ID，必须是被索引的列。DEFAULT
设置默认值，不能将函数作为默认值，必须是常量 。-
引擎类型
- InnoDB：可靠的事务处理引擎，不支持全文本搜索。
- MEMORY：数据存储在内存中，速度快，适用于临时表。
- MyISAM：性能极高，支持全文本搜索，不支持事务。
引擎可以混用，但外键不能跨引擎。
显示单表信息
show table status like 表名;
显示创建表的语句：
SHOW CREATE TABLE 表名;
- 显示表中的所有列
show full columns 表名;
修改表结构
-
修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称 -
新增列
ALTER TABLE 表名 ADD 列名 varchar(255) -
修改列
-
删除列
ALTER TABLE 表名 DROP COLUMN 列名 -
添加外键
ALTER TABLE t_course ADD CONSTRAINT fk_course_teacher FOREIGN KEY (course_teacher_ID) REFERENCES t_teacher (teadher_ID)
删除表
DROP TABLE t_course;
TRUNCATE TABLE t_course;
DELETE TABLE t_course;
DELETE
DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作，不会减少表和索引所占用的空间，如果有相应的 tigger，执行的时候将被触发。TRUNCATE
执行后表和索引所占用的空间会恢复到初始大小，且不会激发触发器。DROP
删除整个表（结构和数据）包括约束（constrain),触发器（trigger)索引（index)；依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。-
3者总结
- delete 和 truncate 只删除表的数据不删除表的结构
- 速度,一般来说: drop > truncate > delete
- delete语句是DML，这个操作会放到rollback segement中，事务提交之后才生效。如果有相应的trigger，执行的时候将被触发
truncate，drop是DDL，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。
重命名表
RENAME TABLE course to t_course;
数据查询
-
INWHERE column IN (value1,value2,...) WHERE column NOT IN (value1,value2,...) - 去重
DISTINCTSELECT DISTINCT vend_id FROM products // 去重 SELECT prod_name FROM products LIMIT 5, 10 // [5, 5+10] - 排序:
ORDER BYSELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_nameDESC降序，默认升序 - %匹配任意(包括0)多个字符， _匹配单个字符
- 分组
连接查询
TODO
数据插入
- 插入一条
INSERT INTO t_course VALUE(......); INSERT INTO t_course(cno, cname, tno) VALUE(......); - 一次插入多条
这种方式插入多条语句比一次插入一条多次插入的快INSERT INTO t_course VALUES(......),(......),(......); INSERT INTO t_course(cno, cname, tno) VALUES(......),(......),(......); - 插入检索出的数据
INSERT INTO
数据更新
UPDATE t_course SET course_name = 'OS' WHERE course_ID = 123;
数据删除
DELETE FROM t_course WHERE course_ID = 123;
索引
-
显示表中已建的索引
show index from 表名; -
普通索引
最基本索引，没有任何限制。create index 索引名称 on 表名(列名); -
唯一索引
索引列的值必须唯一，可以有空值。主键是特殊的唯一索引（不能为空值）。create UNIQUE index 索引名称 on 表名(列名); -
组合索引
就是在多个字段上联合建立一个索引。create index 索引名称 on 表名(列名1,列名2, 列名3...);比如
create index index_union on index_table(name,age,phoneNum);
这里一个组合索引，相当于在有如下三个索引：【name】【name，age】【name，age，phoneNum】 删除索引
LIMIT
LIMIT 主要用于查询之后显示返回指定几条数据。
SELECT * FROM A LIMIT 3, 4 // 表示返回第3条为起点，往后4条数据
- 一个经典的问题：
LIMIT 0, 100与LIMIT 100000, 100的执行效率是一样吗？
不一样，LIMIT 100000, 100实际扫描的1000100行记录，然后只会返回100条记录。
如何优化？
【1】如果是有序的，则可以使用between，例如select * from A where id between 100000 and 1000100
【2】或者用倒序，假设我们有120w记录，采取select * from A order by oid desc limit 100
日期和时间类型
MySQL 提供2种日期类型：DATETIME和TIMESTAMP。
DATETIME
这个类型能保存大范围的值，从1001年到9999年，精度为秒，它把日期时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关，使用8个字节的存储空间。
默认情况下，MySQL以一种可排序的，无歧义的格式显示DATETIME值，例如 "2018-01-16 22:37:08"，这是ANSI标准定义的日期和时间表示方法。-
TIMESTAMP
TIMESTAMP类型保存了从1970年1月1日午夜（格林尼治时间）以来的秒数，和UNIX时间戳相同，TIMESTAMP使用4个字节的存储空间。
因此它的范围小的多，只能表示从1970 ~ 2038年，TIMESTAMP 显示的值依赖于时区，MySQL服务器，操作系统，以及客户端连接都有的时区设置。TIMESTAMP 也有 DATETIME 没有的特殊属性：
- 当字段定义为
timestamp，表示该字段在插入和更新时都不会自动设置为当前时间。 - 当字段定义为
timestamp DEFAULT CURRENT_TIMESTAMP，表示该字段仅在插入且未指定值时被赋予当前时间。 - 当字段定义为
timestamp ON UPDATE CURRENT_TIMESTAMP，表示该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。 - 当字段定义为
timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，表示该字段在插入或更新时未指定值，则被赋值为当前时间。
- 当字段定义为
- 日期和时间的函数
SELECT Now(); // 返回当前日期时间 SELECT Date(Now()); // 返回一个日期时间的日期部分 SELECT Time(Now()); // 返回一个日期时间的时间部分 SELECT CurDate(); // 返回当前日期 SELECT Year(CurDate()) // 返回一个日期或者日期时间的年 SELECT Month(CurDate()) // 返回一个日期或者日期时间的月份 SELECT CurTime(); // 返回当前时间 SELECT Hour(CurTime()); // 返回一个时间或者日期时间的小时部分 SELECT Minute(CurTime()) // 返回一个时间或者日期时间的分部分 SELECT Second(CurTime()) // 返回一个时间或者日期时间的秒部分 SELECT Date_Format(Now(), '%Y-%m-%d'); // 时间转字符串 SELECT Str_To_Date('2017-08-02', '%Y-%m-%d'); // 字符串转时间 SELECT Unix_Timestamp(Now()); // 时间转时间戳 SELECT From_Unixtime(1451997924); // 时间戳转时间
常用函数
- 字符串
- 数值处理
事务
- 开启事务
使用start transaction；或者begin；开启一个新的事务； - 提交事务
commit，提交当前事务,使其持久化； - 回滚事务
rollback，回滚当前事务。
下面举个例子:
begin;
......
commit;
通过 begin 或 start transaction 命令只是开启了一个事务，如果没有commit 的话，其实对数据是没有真正的修改的(可以使用Navicat工具,同时开启两个查询窗口来验证)。
视图
触发器
存储过程
安全管理
MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多，又不能少。
-
管理用户
MySQL用户账号和信息存储在名为mysql的数据库中（一般不需要直接访问mysql数据库和表）mysql的数据库中有一个名为user的表，它包含了用户账号。use mysql; SELECT user FROM user; +-------+- 创建用户
创建用户，使用CREATE USER语句CREATE USER username BY password; - 用户账号重命名
RENAME USER olduser To newuser; - 删除用户
DROP USER user; - 修改密码
SET PASSWORD FOR username = Password('password'); # Password()是一个函数
- 创建用户
-
设置访问权限
在创建用户账户后，必须接着分配访问权限，新创建的用户账号没有访问权限，它们只能登录MySQL，但不能看到数据，不能执行任何数据库操作。为了设置权限，使用
GRANT语句，GRANT语句要求你至少给出以下信息：- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名
GRANT SELECT ON test.* to tiger # 允许用户在test上的所有表使用SELECT flush privileges; # 刷新权限- 查询某个用户的权限
SHOW GRANTS FOR tiger; -
撤销访问权限
GRANT的反操作为REVOKE，用它来撤销特定的权限。被撤销的权限一定要存在。REVOKE SELECT ON test.* to tiger -
权限
GRANT 和 REVOKE 可在几个层次上控制访问权限：- 整个服务器，使用 GRANT ALL 和 REVOKE ALL
- 整个数据库，使用 ON database.*
- 特定的表，使用 ON database.table
- 特定的列
- 特定的存储过程
