创建数据库
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT]
创建带编码的数据库
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARACTER SET = 'GBK';
修改指定数据库的编码方式
ALTER DATABASE maizi1 DEFAULT CHARACTER SET utf8;
查看当前数据库
SHOW DATABASES;
SHOW SCHEMA;
查看数据库的编码
SHOW CREATE DATABASE dbname;
SHOW CREATE SCHEMA dbname;
打开指定数据库
USE dbname;
当前打开的数据的名称
SELECT DATABASE();
删除指定数据库
DROP DATABASE dbname;
DROP DATABASE IF EXISTS dbname;
查看警告
SHOW WARNINGS;
创建数据表
带主键 增长 非空 默认值 唯一
CREATE TABLE IF NOT EXISTS dbname(id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
userame VARCHAR(20) NOT NULL ,
pwd CHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '113333@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(200) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') DEFAULT '保密',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
)ENGINE=MyISAM CHARSET=UTF8 AUTO_INCREMENT=100;
查看数据库下的数据表
SHOW TABLES
查看指定表的表结构
DESC dbname
DESCRIBE dbname
SHOW COLUMNS FROM dbname
无符号
id TINYINT UNSIGNED
零填充
id TINYINT ZEROFILL
修改自增长值
ALTER TABLE taname AUTO_INCREMENT;
修改表名
ALTER TABLE oldTaname RENAME newTaname;
RENAME TABLE oldTaname to newTaname;
增加字段
ALTER TABLE dbname ADD age CHAR(20) NOT NULL FIRST;
ALTER TABLE dbname ADD age CHAR(20) NOT NULL AFTER name;
ALTER TABLE dbname ADD age CHAR(20) NOT NULL AFTER name,ADD sex ENUM('男','女','保密');
删除字段
ALTER TABLE dbname DROP age;
ALTER TABLE dbname DROP sex，DROP age;
添加删除一起
ALTER TABLE dbname ADD age CHAR(20) NOT NULL AFTER name,DROP sex;
修改字段属性
ALTER TABLE dbname MODIFY age TINYINT DEFAULT 18 AFTER pwd;
修改字段名称(也能完成MODIFY的功能)
ALTER TABLE dbname CHANGE age age1 TINYINT DEFAULT 10;
添加修改删除默认值
ALTER TABLE dbname ALTER age SET DEFAULT 12;
ALTER TABLE dbname ALTER age DROP DEFAULT;
添加主键，删除主键
ALTER TABLE dbname ADD PRIMARY KEY(id);
ALTER TABLE dbname DROP PRIMARY KEY;
添加唯一，删除唯一
ALTER TABLE dbname ADD UNIQUE(age);
ALTER TABLE dbname ADD CONSTRAINT symbol UNIQUE KEY age(myage);
ALTER TABLE dbname ADD CONSTRAINT symbol INDEX muIUni_test_test1
(age,myage);
ALTER TABLE dbname DROP KEY myage;
修改存储引擎
ALTER TABLE dbname ENGINE=MyISAM;
修改自增长值
ALTER TABLE dbname AUTO_INCREMENT=100;
对表数据操作（增删改查）
插入一条数据
INSERT dbname VALUES(值....) 每个字段都赋值
INSERT dbname(字段1....) VALUES(值1....)
INSERT dbname SET id=1,username='',pwd='111';
插入多条数据
INSERT dbname VALUES(值....),(值....),(值....); 每个字段都赋值
INSERT dbname(字段1....) VALUES(值1....),(值1....),(值1....);
将查询结果插入到表中
INSERT dbname SELECT id,username FROM db1name;
更新记录
UPDATE dbname SET age=88 WHERE id=2;
删除记录
DELETE FROM dbname WHERE id=1;
DELETE FROM dbname WHERE sex='n' ORDER BY age ASC LIMIT 1;
TRUNCATE TABLE dbname; 重置自增长值,彻底清空
单表查询
SELECT * FROM dbname;
SELECT id,username FROM dbname;
SELECT id,username FROM dtbase.dbname;
SELECT dbname.id,dbname.username FROM dtbase.dbname;
给表名起别名
SELECT db.id,db.username FROM dbname AS db;
给字段起别名
SELECT id AS '编号',username AS ‘名称’ FROM dbname;
检测值是否为NULL
SELECT * FROM user WHERE age <=> NULL;
SELECT * FROM user WHERE age IS NULL;
查询范围之间的
SELECT * FROM user WHERE id BETWEEN 1 ADD 100;
SELECT * FROM user WHERE id IN(1,3,5,7,9,10);
SELECT * FROM user WHERE username IN('一','二','三');
SELECT * FROM user WHERE username NOT IN('一','二','三');
模糊查询LIKE
%：代表0个一个或多个任意字符
_:代表一个任意字符
SELECT * FROM user WHERE username LIKE '张%';
SELECT * FROM user WHERE username LIKE '___';
逻辑运算符
SELECT * FROM user WHERE id=1 AND username IS NOT NULL;
SELECT * FROM user WHERE id=1 OR username IS NOT NULL;
分组查询
SELECT * FROM user GROUP BY sex;
SELECT * FROM user GROUP BY sex,age;
SELECT * FROM user WHERE id >=5 GROUP BY sex;
配合聚合函数
统计每个分组的总数
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers FROM user GROUP BY sex;
统计总数据条数
SELECT COUNT(*) AS count FROM user;
COUNT(字段)不统计null值
SELECT COUNT(age) AS count FROM user;
统计各组中的最大值，最小值，总和，平均值
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM user GROUP BY sex;
WITH ROLLUP对分组之后再统计,在最后加一条记录
SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM user GROUP BY sex WITH ROLLUP;
分组结果进行二次筛选（只能配合分组使用）
人数大于2的
SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user GROUP BY sex
HAVING COUNT(*)>2;
对查询结果进行排序
SELECT * FROM user ORDER BY id ASC;
SELECT * FROM user ORDER BY id DESC;
SELECT * FROM user ORDER BY age ASC,id DESC;
SELECT age,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers,
MAX(age) AS max_age
FROM user
WHERE id >3
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age ASC,id DESC;
实现随机提取记录
SELECT * FROM user ORDER BY RAND();
通过limit限制显示条数
查询表中的前3条记录
SELECT * FROM user LIMIT 3;
查询第3条记录
SELECT * FROM user LIMIT 2,1;
单表查询select的完整形式
SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM user
WHERE age>10
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age ASC,id DESC
LIMIT 0,1;
内连接查询
SELECT u.*,c.id From user AS u,class AS c WHERE u.proid=c.id;
SELECT u.*,c.id From user AS u INNER JOIN class AS c ON u.proid=c.id;
SELECT u.*,c.id From user AS u CROSS JOIN class AS c ON u.proid=c.id;
SELECT u.*,c.id From user AS u JOIN class AS c ON u.proid=c.id;
SELECT u.sex,u.age,c.id,GROUP_CONCAT(u.username) AS users,
COUNT(*) AS totalusers
From user AS u
JOIN class AS c
ON u.proid=c.id
WHERE age>10
GROUP BY sex
HAVING COUNT(*)>=0
ORDER BY age ASC;
外连接查询
LEFT[OUTER]JOIN 显示左表中全部记录及右表符合连接条件的记录
RIGHT[OUTER]JOIN 显士右表中全部记录及左表符合连接条件的记录
SELECT u.*,c.id From user AS u LEFT JOIN class AS c ON u.proid=c.id;
SELECT u.*,c.id From user AS u RIGHT JOIN class AS c ON u.proid=c.id;
外键FOREIGN KEY(proid) REFERENCES dbkey(id)
主表
CREATE TABLE IF NOT EXISTS dbkey(id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
从表 city CHAR(30) NOT NULL)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS dbname(id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
userame VARCHAR(20) NOT NULL ,
pwd CHAR(30) NOT NULL,
proid SMALLINT UNSIGNED,
CONSTRAINT dbkey_dbname FOREIGN KEY(proid)
REFERENCES dbkey(id)
)ENGINE=INNODB;
删除外键
ALTER TABLE dbname DROP FOREIGN KEY dbkey_dbname;
添加外键
ALTER TABLE dbname ADD CONSTRAINT dbkey_dbname FOREIGN KEY(proid) REFERENCES
dbkey(id);
从父表删除或更新记录，字表中的的相关记录自动变化CASCADE
CREATE TABLE IF NOT EXISTS dbname(id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
userame VARCHAR(20) NOT NULL ,
pwd CHAR(30) NOT NULL,
proid SMALLINT UNSIGNED,
CONSTRAINT dbkey_dbname FOREIGN KEY(proid)
REFERENCES dbkey(id) ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;