准备
登陆
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='游戏'
-> );
