- 允许root远程登录，密码是kdm001
Grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'kdm001' with grant option;
flush privileges;
- 修改提示符
mysql --prompt="\d > "
- 查看数据库参数
STATUS
- 查看字符编码
SHOW VARIABLES LIKE 'char%';
- 创建数据库
CREATE DATABASE just4test;
- 查询数据库的编码方式
SHOW CREATE DATABASE just4test;
- 修改数据库的编码方式
ALTER DATABASE just4test CHARACTER SET utf8;
- 创建数据表 userinfo：id作为主键且自动递增，name要求唯一，性别是枚举类型，默认值为保密
CREATE TABLE userinfo(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE KEY,
age SMALLINT NOT NULL,
sex ENUM('男','女','保密') default '保密'
);```
> 1. 查看表结构```SHOW COLUMNS FROM userinfo;```
> 1. 插入一条记录```INSERT userinfo(name,age,sex) values('王尼玛',26,'男');```
> 1. 插入多条记录```INSERT userinfo values(DEFAULT,'同同',26,DEFAULT),(DEFAULT,'唐马儒',30,'男');```
> 1. INSERT...SELECT结构```INSERT userinfo_sex(sex) SELECT sex FROM userinfo GROUP BY sex;```
> 1. 删除一条记录```delete from userinfo where id=1;```
> 1. 创建具有外键约束的子表 userinfo_extra：
```sql
CREATE TABLE userinfo_extra(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
uid INT UNSIGNED,
nickname VARCHAR(50) NOT NULL,
FOREIGN KEY (uid) REFERENCES userinfo(id)
);
- 在数据表中增加一列
ALTER TABLE userinfo ADD nickname VARCHAR(50);
- 在数据表中删除一列
ALTER TABLE userinfo DROP nickname;
- 添加主键约束
ALTER TABLE tb4test ADD PRIMARY KEY (id);
- 删除主键约束
ALTER TABLE tb4test DROP PRIMARY KEY;
- 添加唯一约束
ALTER TABLE tb4test ADD UNIQUE KEY (name);
- 删除唯一约束
ALTER TABLE tb4test DROP KEY name;
- 修改表的默认约束
ALTER TABLE userinfo ALTER sex SET DEFAULT '男';
- 删除表的默认约束
ALTER TABLE userinfo ALTER sex DROP DEFAULT;
- 修改数据表名称
ALTER TABLE tb4test RENAME tb4test_renamed;
- 修改列名称及列定义
ALTER TABLE tb4test_renamed CHANGE id p_id SMALLINT
- 更新记录
UPDATE userinfo SET sex = '男' WHERE id = 10;
- 删除记录
DELETE FROM userinfo WHERE sex = '保密';
- 对于查询结果进行分组
SELECT age FROM userinfo GROUP BY age;
- 对于查询结果进行排序
SELECT name,age FROM userinfo ORDER BY age;
- 限制查询的结果，从第3条记录开始，连续返回4条记录
SELECT * FROM userinfo LIMIT 3,4;
- 从表中筛选出数据插入另一张表
INSERT tb4test_renamed(name) SELECT name FROM userinfo where sex = '男';
- 子查询
SELECT name,age FROM userinfo WHERE age > any (SELECT age FROM userinfo WHERE age > 25);
