创建数据库:create database [if not exists] t1 character set=utf8 显示创建数据库语句:show create database t1
修改数据库:alter database t1 character set utf8
删除数据库:drop database if exists t1
创建表:
CREATE TABLE tb2( id INT UNSIGNED auto_increment PRIMARY KEY, username VARCHAR(20) not NULL );UNSIGNED:无符号类型
auto_increment:自增,必须与主键配合使用
CREATE TABLE IF NOT EXISTS tb3( id int UNSIGNED auto_increment PRIMARY KEY , username VARCHAR(20) NOT NULL UNIQUE KEY, salary FLOAT(6,2) ); SHOW COLUMNS FROM tb3唯一约束:UNIQUE KEY可为空,但可保证值的唯一性,UNIQUE KEY+NOT NULL=PRIMARY KEY主键约束:PRIMARY KEY不可为空,且唯一,可与其他列共同组成复合主键
CREATE TABLE IF NOT EXISTS tb4( id int UNSIGNED auto_increment PRIMARY KEY , username VARCHAR(20) NOT NULL UNIQUE KEY, sex enum('1','2','3') DEFAULT 3 ); SHOW COLUMNS FROM tb4; INSERT INTO tb4(username) VALUES('Tom'); SELECT * from tb4;默认约束:当没有为字段赋值时,会自动给他赋值外键约束:
CREATE TABLE IF NOT EXISTS province( id INT UNSIGNED auto_increment PRIMARY KEY, pname VARCHAR(20) NOT NULL ); CREATE TABLE if not EXISTS user( id INT UNSIGNED auto_increment PRIMARY KEY, usrename VARCHAR(20) not NULL, pid INT UNSIGNED NOT NULL, FOREIGN KEY(pid) REFERENCES province(id) );外键约束的要求:1、子表和父表必须要有相同的存储引擎
2,、数据表的存储引擎必须为InnoDB
3、主键列必须和外键列具有相似的数据类型
4、外键列和主键列必须创建索引如果没有创建,mysql会默认创建
修改表:
添加单列:
ALTER TABLE user ADD age INT UNSIGNED NOT NULL添加多列:
ALTER TABLE user ADD password VARCHAR(40) NOT NULL,ADD age int UNSIGNED NOT NULL 删除单列: ALTER TABLE user DROP age;修改列名:
ALTER TABLE user CHANGE usrename username VARCHAR(20) not NULL查询列:
别名:
SELECT id AS id ,username AS uname from user 查询结果分组: SELECT age from USER GROUP BY age分组条件: SELECT age from USER GROUP BY age HAVING age<21 分组结果排序: SELECT * from user ORDER BY id DESC 限制结果返回集: SELECT * from USER LIMIT 3,2\\返回第三条开始的两条数据子查询:是指出现在其他sql语句内的select语句
比较运算产生的子查询:
SELECT goods_id,goods_name,goods_price from tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods); IN和NOT IN引发的子查询: SELECT goods_id,goods_name,goods_price from tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_price>6000); 查询并插入:INSERT.....SELECT..... INSERT into tdb_goods_cates(cate_name) SELECT goods_cate from tdb_goods GROUP BY goods_cate; 内连接:仅显示符合条件的记录外连接:
左外连接:显示左表的全部记录以及右表符合记录的记录(右表无匹配的显示为null)
右外连接:显示右表的全部记录以及左表符合记录的记录
单表模拟多表删除:
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;