创建数据库: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;