mysql命令行操作

准备

登陆

mysql -uroot -p

数据库操作

查看现有数据库

mysql> show databases;

创建数据库

mysql> create database test character set utf8;

删除数据库

mysql> drop database test;

查看数据库

mysql> show create database test;

使用数据库

mysql> use test;

查看当前数据库

mysql> select database();

表操作

新建表

mysql> create table t_person(
    -> id int(32) primary key auto_increment,
    -> name varchar(32),
    -> age int(8)
    -> );

查看现有表

mysql> show tables;

查看表结构

mysql> desc t_person;

添加字段

mysql> alter table t_person add number int(32);

修改字段

mysql> alter table t_person modify number int(64);

修改字段名

mysql> alter table t_person change number no varchar(32);

删除列

mysql> alter table t_person drop no;

修改表名

mysql> rename table t_person to t_student;

修改字符集

mysql> alter table t_student character set gbk;

删除表

mysql> drop table t_person;

数据操作

插入数据

mysql> insert into t_student (id,name,age) values(1,'小华',16);

查看数据

mysql> select * from t_student;

修改某列的值(不加where指的是全部数据)

mysql> update t_student set age=18 where id=1;

删除数据

mysql> delete from t_student where id=1;

查询数据

mysql> select name,age from t_student;
mysql> select * from t_student where age<16;
mysql> select * from t_student where name='小花';
mysql> select * from t_student where name like '小%';
mysql> select * from t_student where id in(1,2,3);
mysql> select * from t_student where id=1 or age<15;

排序

mysql> select * from t_student order by age asc;
mysql> select * from t_student order by age desc;

统计

mysql> select sum(age) from t_student;
mysql> select avg(age) from t_student;
mysql> select count(*) from t_student;

分组统计

+----+--------+------+------+
| id | name   | age  | sex  |
+----+--------+------+------+
|  1 | 小华   |   16 |    0 |
|  2 | 小明   |   15 |    0 |
|  3 | 小花   |   17 |    1 |
+----+--------+------+------+

mysql> select sex,count(*) from t_student group by sex;
mysql> select sex,avg(age) from t_student group by sex having avg(age)<16;

关联表操作

准备数据

mysql> create database db_product character set utf8;
mysql> use db_product;

mysql> create table t_product(
    -> id int(32) primary key auto_increment,
    -> category_id int(32) not null,
    -> name varchar(50),
    -> price double
    -> );

mysql> create table t_category(
    -> id int(32) primary key auto_increment,
    -> name varchar(50)
    -> );

mysql> insert into t_category values(null,'食品');
mysql> insert into t_category values(null,'数码');
mysql> insert into t_category values(null,'游戏');

mysql> insert into t_product values(null,1,'汉堡',20);
mysql> insert into t_product values(null,2,’王者荣耀点卡',99);
mysql> insert into t_product values(null,3,’手机',3000);

mysql> select * from t_category;
+----+--------+
| id | name   |
+----+--------+
|  1 | 食品   |
|  2 | 数码   |
|  3 | 游戏   |
+----+--------+

mysql> select * from t_product;
+----+-------------+--------------------+-------+
| id | category_id | name               | price |
+----+-------------+--------------------+-------+
|  1 |           1 | 汉堡               |    20 |
|  2 |           3 | 王者荣耀点卡       |    99 |
|  3 |           2 | 手机               |  3000 |
+----+-------------+--------------------+-------+

建立关联

mysql> alter table t_product add foreign key(category_id) references t_category(id);

删除操作(删除使用中的主表内容会报错)

mysql> delete from t_category where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_product`.`t_product`, CONSTRAINT `t_product_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `t_category` (`id`))

关联查询

mysql> select * from t_product where category_id=(
    -> select id from t_category where name='游戏'
    -> );