mysql语法(更新中...)

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;

图1
16.截取字符串
SELECT mid('abcdefg', 2, 2);
<small>参数1:待截取的字符
参数2:截取的下标位置
参数3:截取的长度</small>
图2

17.给表重命名
alter table old_name rename new_name;
18.添加列
alter table test add lie int(4) not null;

图3

19.修改列名
alter table test_test change lie id varchar(10) not null;

图4

20.复制表结构
CREATE TABLE 新表 LIKE 旧表