mysql随笔

允许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);

mysql随笔

推荐阅读更多精彩内容