1.显示所有数据库
SHOW DATABASES;
2.选择数据库
USE blue;
3.显示所有表
SHOW TABLES;
4.创建表
CREATE TABLE people(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
gender ENUM('F','M')
)default charset=utf8;
5.删除表
DROP TABLE people
6.显示表结构
DESCRIBE people;
7.插入数据
INSERT INTO people (name, gender) VALUES('张三', 'M');
8.查找数据
SELECT * FROM people WHERE id > 5;
9.删除数据
DELETE FROM people WHERE id > 5;
10.更新数据
UPDATA people SET name = '李四'
11.AND
<small>一般用在WHERE语句中</small>
SELECT * FROM people WHERE id > 5 AND id < 10;
12.OR
<small>一般用在WHERE语句中</small>
SELECT * FROM people WHERE id > 5 OR id < 10;
13.排序
<small>默认为ASC,升序排列;DESC降序</small>
SELECT * FROM news ORDER BY id DESC
14.限制查找的数量和查找的位置
<small>offset:查找起始位置的下标
length:查找的长度</small>
SELECT * FROM news ORDER BY id DESC LIMIT $offset, $length
15.求某字段的平均数
INSERT INTO test01 (nuber) VALUES ('1'), ('3'), ('5'), ('5');
SELECT avg(nuber) FROM test01;
16.截取字符串
SELECT mid('abcdefg', 2, 2);<small>参数1:待截取的字符
参数2:截取的下标位置
参数3:截取的长度</small>
17.给表重命名
alter table old_name rename new_name;
18.添加列
alter table test add lie int(4) not null;
19.修改列名
alter table test_test change lie id varchar(10) not null;
20.复制表结构
CREATE TABLE 新表 LIKE 旧表
