MySQL常用知识点总结1

##创建用户
CREATE USER 'sampadmin'@'localhost' IDENTIFIED BY 'root';

##授权
GRANT ALL ON sampdb.* TO 'sampadmin'@'localhost';

##连接MySQL服务器
mysql -h localhost -p -u 

##查询当前日期和时间
SELECT NOW(); 
SELECT NOW() \g

##显示当前登录用户，mysql版本
SELECT VERSION(),USER() \G

##创建数据库
CREATE DATABASE db;

##查询当前使用数据库
SELECT DATABASE();

##使用某个数据库
USE sampdb;

##创建表
CREATE TABLE president(
	last_name VARCHAR(15) NOT NULL,
	first_name VARCHAR(15) NOT NULL,
	suffix VARCHAR(5)  NULL,
	city VARCHAR(20) NOT NULL,
	state VARCHAR(2) NOT NULL,
	birth DATE NOT NULL,
	death DATE NULL
)

CREATE TABLE member(
	member_id INT UNSIGNED  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY （member_id）,
	last_name VARCHAR(15) NOT NULL,
	first_name VARCHAR(15) NOT NULL,
	suffix VARCHAR(5)  NULL,
	expiration DATE NULL,
	email VARCHAR(100) NULL,
	street VARCHAR(20) NULL,
	city VARCHAR(20) NULL,
	state VARCHAR(2) NOT NULL,
	zip VARCHAR(20) NULL,
	intrests VARCHAR(255) NULL
)


##查看表结构
DESCRIBE president;
DESC president;
EXPLAIN president;
SHOW COLUMNS FROM president;
SHOW FIELDS FROM president;
SHOW FULL COLUMNS  fom president;

##显示使用数据库下的表
SHOW TABLES;
##显示服务器下数据库
SHOW DATABASES;

##模式匹配 mysql不会区分大小写
SELECT * FROM president WHERE last_name LIKE 'W%';
SELECT * FROM president WHERE last_name LIKE '%W%';
SELECT * FROM president WHERE last_name LIKE '____';

##设置和使用自定义变量  eg:@variable_name := column_name
SELECT @jackson_birth := birth FROM president
WHERE last_name='Jackson' AND first_name='Andrew';
#使用变量
SELECT * FROM president WHERE birth <@jackson_birth ORDER BY birth;
##使用变量赋值
SET @today=CURDATE();
SET @onw_week_ago := DATE_SUB(@today,INTERVAL 7 DAY);
SELECT @today,@onw_week_ago;


##统计
##dinstinct 去除重复值
SELECT DISTINCT state FROM president;
##count 查询行数 count(列名),只统计非null数量
SELECT COUNT(*) FROM member;
SELECT COUNT(*) , COUNT(email),COUNT(expiration) FROM member;
SELECT COUNT(DISTINCT state) FROM president;


##分组统计
SELECT sex,COUNT(*) FROM student GROUP BY sex;
SELECT MONTH(birth) AS `month`,MONTHNAME(birth) AS `name`,
COUNT(*) AS `count` FROM president GROUP BY `name` ORDER BY `month`;
## with rollup  汇总统计
SELECT sex,COUNT(*) FROM student GROUP BY sex WITH ROLLUP;


##查看服务器SQL模式
SELECT @@session.sql_mode;
SELECT @@global.sql_mode;

##查看服务器当前字符集和排序规则
SHOW VARIABLES LIKE 'character\_set\_%';
SHOW VARIABLES LIKE 'collation\_%';

##查看现有数据库定义
SHOW CREATE DATABASE `mysql技术内幕`;
##查看服务器有哪些存储引擎
SHOW ENGINES;

##创建表
SHOW CREATE TABLE president ;

##修改表的引擎
ALTER TABLE presidnet ENGINE=INNODB;
##创建临时表
CREATE TEMPORARY TABLE xx;

##根据其他表或者查询结果创建表
CREATE TABLE student_copy LIKE student;
INSERT INTO student_copy SELECT * FROM student;

##Cast 函数 转换函数

##删除表
DROP TABLE b1;
DROP TABLE b1,b2,b3;
DROP TABLE IF EXISTS b1;
##删除临时表
DROP TABLE TEMPORARY b1;
##索引表
##创建索引
ALTER TABLE b1 ADD INDEX index_name (index_colums);
CREATE INDEX index_name ON b1 (index_columns);
##删除索引
DROP INDEX index_name ON table_name;
DROP INDEX `primary` ON table_name;
##更改表结构
##更改列数据类型
ALTER TABLE table_name MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE table_name CHANGE i i MEDIUMINT UNSIGNED;
##更改列字符集
ALTER TABLE table_name c CHAR(20) CHARACTER SET ucs2;
##修改表的引擎
ALTER TABLE table_name ENGINE=engine_name;
##重新命名表
ALTER TABLE table_nme RENAME TO new_table_name;
RENAME TABLE table_name TO new_table_name
##重新命名多张表
RENAME TABLE t1 TO new_t1,t2 TO new_t2;
##加上数据库前缀将导致改数据库下的数据表移动到新数据库下
ALTER TABLE mybatis.`user` RENAME TO `test`.a;
RENAME TABLE `mybatis`.`users` TO `test`.b;

##列出可以访问的数据库
SHOW DATABASE;
##显示数据库的create databse语句
SHOW CREATE databse db_name;
##列出数据据库里的表
SHOW TABLES;
SHOW TABLES FROM db_name;
##显示表的create table语句
SHOW CREATE TABLE table_name;
##显示表的列或索引信息
SHOW COLUMNS FROM table_name;
SHOW INDEX FROM table_name;
##显示数据库或数据库表的描述信息
SHOW TABLE STATUS;
SHOW TABLE STATUS FROM db_name;
##查找表的主键
SHOW COLUMNS FROM student WHERE `Key`=`PRI`;
##模糊查找表
SHOW TABLES LIKE 'tb1_name';
SHOW TABLES FROM db_name LIKE 'tb_name';

SHOW TABLES IN information_schema;

##左表里右表无法匹配行找出来
SELECT t1.*,t2.* FROM t1 LEFT JOIN t2 ON t1.i1=t2.2 WHERE t2.i2 IS NULL;

##标量子查询 只有一个值
SELECT * FROM score WHERE event_id = (SELECT
event_id FROM grade_event)
##查询最早出生最早
SELECT * FROM president WHERE bitrh = 
(SELECT MIN(birth) FROM president);
##使用元组
SELECT last_name,first_name,city,state
FROM president WHERE (city,state)=
(SELECT city,state FROM president);
##等价于
SELECT last_name,first_name,city,state
FROM president WHERE ROW(city,state)=
(SELECT city,state FROM president WHERE 
last_name='Adams' AND first_name='John');

##in和not in子查询
SELECT * FROM student 
WHERE student_id IN (SELECT student_id FROM absence);
SELECT * FROM student 
WHERE student_id NOT IN (SELECT student_id FROM absence);
#多列情况
SELECT * FROM president WHERE (city,state) IN (SELECT
city,state FROM president WHERE last_name='Roosevelt');

##all,any,some子查询
##选取出生日期小于或等于表里所有出生日期
SELECT * FROM president WHERE birth <=ALL 
(SELECT birth FROM president);
SELECT * FROM president WHERE birth <=ANY
(SELECT birth FROM president);

##exists,not exists子查询
#查找缺勤学生
SELECT student_id FROM student WHERE 
EXISTS 
(SELECT 1 FROM absence 
WHERE absence.`student_id`=student.`student_id`);
SELECT student_id FROM student WHERE 
NOT EXISTS 
(SELECT 1 FROM absence 
WHERE absence.`student_id`=student.`student_id`);

##from 子句里的子查询
SELECT * FROM (SELECT 1,2) AS t1 INNER JOIN (SELECT 3,4) AS t2;

##子查询修改为连接
SELECT * FROM student LEFT JOIN absence ON
student.`student_id`=absence.`student_id`
WHERE absence.`student_id` IS NULL;
##等价
SELECT * FROM student WHERE student.`student_id`
NOT IN (SELECT student_id FROM absence);