MySQL基础

-- 显示所有数据库SHOW DATEBASES;-- 创建数据库CREATE DATEBASE TESTDB;-- 删除数据库DROP TESTDB;-- 使用数据库USE TESTDB;-- 创建数据表 CREATECREATE TABLE USER (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(40) NOT NULL,password VARCHAR(20) NOT NULL,createdate DATE);-- 删除数据表 DROP  IF EXISTSDROP TABLE USER;-- 插入数据 INSERTINSERT INTO USER (name,password,createdate) VALUES ('Jack',123456,NOW());-- 查询数据 SELECT-- * 通配符-- WHERE 查询条件，WHERE子句的字符串比较是不区分大小写的，除非LIKE，BINARY-- AND 且，OR 或-- LIMIT 属性来设定返回的记录数SELECT name,password FROM USER WHERE id=1 AND name='Rose' LIMIT 5;-- 修改数据 UPDATEUPDATE USER SET name='Lee',password='126543' WHERE id = 1;-- 删除数据 DELETEDELETE FROM USER WHERE id = 1;-- 模糊查询 LIKE-- % 表示任意字符-- LIKE 同样可以使用在DELETE，UPDATE中SELECT * FROM USER WHERE name LIKE '%se';-- 排序 ORDER BY-- 使用任意字段（或多个字段）作为排序条件-- 可以使用 WHERE .. LIKE 设置条件-- ASC 升序、DESC 降序SELECT * FROM USER ORDER BY name DESC;-- 分组 GROUP BY-- COUNT，SUM，AVG，函数-- 分组统计数据基础上再进行相同统计 WITH ROLLUP -- coalesce 设置取代NULLSELECT name, COUNT(*) FROM USER GROUP BY name;SELECT name,SUM(signin) as signin_count FROM USER GROUP BY name WITH ROLLUP;SELECT coalesce(name,'总数'),SUM(signin) as signin_count FROM USER GROUP BY name WITH ROLLUP;-- 多表读取数据,多表连接 JOIN-- INNER JOIN 内连接：获取两个表中字段匹配关系的记录。INNER可以省略-- LEFT JOIN 左连接：获取左表所有记录，即使右表没有对应匹配的记录-- RIGHT JOIN 右连接：获取右表所有数据，即使左表没有对应匹配的记录SELECT a.id,a.author,b.count FROM table1 a INNER JOIN table2 b ON a.author = b.author;-- 上面的SQL等价与：SELECT a.id,a.author,b.count FROM table1 a, table2 b WHERE a.author = b.author;SELECT a.id,a.author,b.count FROM table1 a LEFT JOIN table2 b ON a.author = b.author;-- NULL值处理-- IS NULL 返回 true,false-- IS NOT NULL -- <==> 比较操作符，当比较的两个值为NULL时返回true-- 不能使用 = NULL 或者 != NULL 查找NULL值-- NULL 值于其他值比较永远返回FALSE，包括与NULL比较SELECT * FROM USER WHERE count IS NULL;SELECT * FROM USER WHERE count IS NOT NULL;-- 正则表达式 REGEXP-- ^ 匹配字符串开始位置。如果设置了Multiline属性，也可以匹配\n,\r之后的位置-- $ 匹配字符串结束位置。.... 同上 ...........................之前.....-- . 匹配\n之外的任意单个字符。若要匹配‘\n’，使用'[.\n]'模式-- [...]匹配所包含的任意一个字符。-- [^...]匹配未包含的的任意一个字符。-- p1|p2|p3 匹配p1或者p2或者p3-- * 匹配前面的自表达式0次或者多次 zo* 能匹配 z以及zoo。等价于{0,}-- + 匹配前面的表达式1次或者多次 {1,}-- {n} 匹配确定的n次。o{2} 能匹配 food,但是不能匹配Bob-- {n,m} 最少匹配n次，最多匹配m次。SELECT name FROM USER WHERE name REGEXP '^[aeiou]|ok$'; -- aeiou开头并且ok结尾-- 事务-- Atomicity原子性：一组事务，要么成功，要么撤回-- Consistency稳定性：有非法数据，事务撤回-- Isolation隔离性：独立运行，事务100%隔离-- Durability可靠性：-- 修改数据表名或者修改数据表字段 ALTERALTER TABLE USER DROP createdate; -- 删除数据表的createdate字段ALTER TABLE USER ADD createdate DATE; --在数据表中添加createdate字段。默认添加在末尾ALTER TABLE USER ADD createdate DATE FIRST; -- 加在第一列ALTER TABLE USER ADD createdate DATE AFTER name; -- 添加在name列后面ALTER TABLE USER MODIFY name CHAR(10); -- 修改字段类型ALTER TABLE USER CHANGE name fullname CHAR(20); -- 把name改为 fullname CHAR(20);ALTER TABLE USER CHANGE fullname fullname CHAR(40); -- 修改fullname 类型ALTER TABLE USER MODIFY j BIGINT NOT NULL DEFAULT 100; -- 非空，默认值100ALTER TABLE USER ALTER i SET DEFAULT 1000; -- 修改字段默认值ALTER TABLE USER ALTER i DROP DEFAULT; -- 删除默认值ALTER TABLE USER TYPE = MYISAM; -- 修改数据表的类型ALTER TABLE USER RENAME TO CUSTOMER; -- 修改表名-- 索引INDEX-- 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。CREATE INDEX indexName ON USER(name(length)); -- 创建ALTER USER ADD INDEX indexName ON (name(length)); -- 修改表结构DROP INDEX indexName ON USER;-- 唯一索引CREATE UNIQUE INDEX indexName ON USER(name(length));ALTER USER ADD UNIQUE indexName ON (name(length));-- 添加主键ALTER TABLE USER i INT NOT NULL;ALTER TABLE USER ADD PRIMARY KEY (i);-- 删除主键ALTER TABLE USER DROP PRIMARY KEY;-- 临时表 TEMPORARYCREATE TEMPORARY TABLE salesSummary (id INT NOT NULL PRIMARY KEY,count INT NOT NULL DEFAULT 0);DROP TABLE salesSummary; -- 删除临时表// 会自动销毁-- 服务器元数据SELECT VERSION();SELECT DATEBASE();SELECT USER();SHOW STATUS;SHOW VARIABLES;-- 重置序列ALTER TABLE USER DROP id;ALTER TABLE USER ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);-- 设置序列开始值 创建时设置CREATE TABLE USER (id INT UNSIGNED NOT NULL AUTO_INCREMENT =100);-- 创建后的，修改ALTER TABLE USER id AUTO_INCREMENT = 100;-- 处理重复数据-- 查询重复记录SELECT name ,count(*) as count FROM USER GROUP BY name having count > 1;SELECT * FROM USER WHERE id in (SELECT id FROM USER GROUP BY id having count(id) > 1);-- 过滤重复数据-- DISTINCTSELECT DISTINCT name FROM USER ORDER BY name;-- GROUP BYSELECT last_name,first_name FROM PERSON_TBL GROUP BY (last_name,first_name);-- 删除重复数据-- 新建tmp数据，查询过滤重复数据，删除旧表，重命名新表。-- 防止SQL注入