mysql随笔

1. 允许root远程登录，密码是kdm001

Grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'kdm001' with grant option;
flush privileges;

1. 修改提示符mysql --prompt="\d > "
2. 查看数据库参数STATUS
3. 查看字符编码SHOW VARIABLES LIKE 'char%';
4. 创建数据库CREATE DATABASE just4test;
5. 查询数据库的编码方式SHOW CREATE DATABASE just4test;
6. 修改数据库的编码方式ALTER DATABASE just4test CHARACTER SET utf8;
7. 创建数据表 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)
);

1. 在数据表中增加一列ALTER TABLE userinfo ADD nickname VARCHAR(50);
2. 在数据表中删除一列ALTER TABLE userinfo DROP nickname;
3. 添加主键约束ALTER TABLE tb4test ADD PRIMARY KEY (id);
4. 删除主键约束ALTER TABLE tb4test DROP PRIMARY KEY;
5. 添加唯一约束ALTER TABLE tb4test ADD UNIQUE KEY (name);
6. 删除唯一约束ALTER TABLE tb4test DROP KEY name;
7. 修改表的默认约束ALTER TABLE userinfo ALTER sex SET DEFAULT '男';
8. 删除表的默认约束ALTER TABLE userinfo ALTER sex DROP DEFAULT;
9. 修改数据表名称ALTER TABLE tb4test RENAME tb4test_renamed;
10. 修改列名称及列定义ALTER TABLE tb4test_renamed CHANGE id p_id SMALLINT
11. 更新记录UPDATE userinfo SET sex = '男' WHERE id = 10;
12. 删除记录DELETE FROM userinfo WHERE sex = '保密';
13. 对于查询结果进行分组SELECT age FROM userinfo GROUP BY age;
14. 对于查询结果进行排序SELECT name,age FROM userinfo ORDER BY age;
15. 限制查询的结果，从第3条记录开始，连续返回4条记录SELECT * FROM userinfo LIMIT 3,4;
16. 从表中筛选出数据插入另一张表INSERT tb4test_renamed(name) SELECT name FROM userinfo where sex = '男';
17. 子查询SELECT name,age FROM userinfo WHERE age > any (SELECT age FROM userinfo WHERE age > 25);