MySQL学习进阶
存储引擎
mysql数据类型
(1) 数值类型
(2)字符串类型
(3)日期和时间型
sql语言
约束
| 约束类型： | 主键 | 默认值 | 唯一 | 外键 | 非空 |
|---|---|---|---|---|---|
| 关键字： | PRIMARY KEY | DEFAULT | UNIQUE | FOREIGN KEY |
NOT NULL |
主键(PRIMARY KEY)是用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键要求这一行的数据不能有重复且不能为空。
还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识
默认值约束(DEFAULT)规定，当有DEFAULT约束的列，插入数据为空时该怎么办。
DEFAULT约束只会在使用INSERT语句（上一实验介绍过）时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充
唯一约束(UNIQUE)比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。
当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.
外键(FOREIGN KEY)既能确保数据完整性，也能表现表之间的关系。
一个表可以有多个外键，每个外键必须REFERENCES(参考)另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。
在INSERT时，如果被外键约束的值没有在参考列中有对应，比如以下命令，参考列(department表的dpt_name)中没有dpt3，则INSERT失败
非空约束(NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。
在MySQL中违反非空约束，不会报错，只会有警告.
CREATE DATABASE mysql_shiyan; use mysql_shiyan; CREATE TABLE department ( dpt_name CHAR(20) NOT NULL, people_num INT(10) DEFAULT '10', CONSTRAINT dpt_pk PRIMARY KEY (dpt_name) ##约束名 dpt_pk 、主键约束 ); CREATE TABLE employee ( id INT(10) PRIMARY KEY, name CHAR(20), age INT(10), salary INT(10) NOT NULL, phone INT(12) NOT NULL, in_dpt CHAR(20) NOT NULL, UNIQUE (phone), CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) ##外键约束 ); CREATE TABLE project ( proj_num INT(10) NOT NULL, proj_name CHAR(20) NOT NULL, start_date DATE NOT NULL, end_date DATE DEFAULT '2017-08-15', of_dpt CHAR(20) REFERENCES department(dpt_name), CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name) );
表的操作
1、创建表
CREATE TABLE 表名 （属性名 数据类型 [完整性约束条件]，属性名 数据类型 [完整性约束条件]，属性名 数据类型 [完整性约束条件]）2、查看表结构
describe 表名
3、查看表结构
show create table 表名
show create table 表名 \G
4、修改表名
alter table 旧表名 rename [to] 新表名；
5、修改字段的数据类型
alter table 表名 modify 属性名 数据类型；6、修改字段名
alter table 表名 change 旧属性名 新属性名 [新数据类型]；
7、增加字段
alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first] after 属性名2；
8、删除字段
alter table 表名 drop 属性名；
9、删除关联表
（1）删除表的外键约束外键是一个特殊字段，其将某一个表与其父表建立关联关系。在创建表的时候，外键约束就已经设定好了。去掉他们之间的关联关系需要用到下面语句。
alter table 表名 drop foreign key 外键别名；
drop table 表名；当删除有关联关系的表时，用drop table example1 会报错，原因是有外键依赖于该表
例如创建了一个example4表依赖于example1表，example4表的外键stu_id依赖于example1表的主键。example1表时example4表的父表。
如果要删除example4表，必须先去掉这种依赖关系。最简单的办法是先删除子表example4，然后删除父表example1。但这样可能会影响子表的其他数据。
另一种方法是先删除子表的外键约束，然后删除父表。这种方法不会影响子表的其他数据，可以保证数据库的安全。
比如，example4表的外键别名是d_fk，删除example4的外键约束
alter table example4 drop foreign key d_fk;
可以通过show create table example4 \G来查看是否已删除。
然后再执行 drop table example1;
执行成功则表示操作成功。
帮助
\h 查看帮助
\s 查看当前环境
\c 废弃当前输入命令
创建用户并授权
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT {ALL | SELECT | INSERT | UPDATE } ON databasename.tablename TO 'username'@'host'
工作中使用的示例：
BEGIN DECLARE win_id VARCHAR(20); DECLARE win_count VARCHAR(20); SELECT id FROM windows_vps_log ORDER BY id DESC LIMIT 1 into win_id; SELECT count(id) FROM windows_vps_log into win_count; IF win_count>5000000 THEN delete from windows_vps_log where id <= win_id-3000000; END IF; END
SELECT machine_host, CONCAT(lan_ip, ':', `port`) AS "问题机", dial_time FROM client_info WHERE mac IN ( SELECT mac FROM windows_vps_log WHERE invalid = 0 AND TIMESTAMPDIFF(MINUTE, create_time, now()) > 180 ) ORDER BY machine_host desc；
SELECT c.province, b.provinceid, b.city, b.cityid, count(a.dial_type) AS "上独下混" FROM client_info a, area_cities b, area_provinces c WHERE a.machine_area = b.cityid and b.provinceid=c.provinceid GROUP BY b.city,a.dial_type;
mysql日志类型：
1．错误日志 记录启动、运行或停止mysqld时出现的问题
log-error=d:/mysql_log_err.txt
2．查询日志 记录建立的客户端连接和执行的所有语句（包括错误的）。
log=d:/mysql_log.txt
3、二进制日志 记录所有更改数据的语句、还用于主从复制
log-bin=d:/mysql_log_bin
4．慢日志 记录所有执行时间超过long_query_time秒的所有查询
long_query_time =1 #时间
log-slow-queries= d:/mysql_log_slow.txtmy：
mysql不区分表名大小写：
1、用root登录，修改 /etc/my.cnf；
2、在[mysqld]节点下，加入一行： lower_case_table_names=1
3、重启MySQL即可；
修改表的字符编码：
alter table `tablename` convert to character set utf8;
