mysql 基础笔记

创建数据库：create database 数据库名 [其他选项];

查看已有数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| test               |
+--------------------+
4 rows in set (0.00 sec)


使用数据库：use 数据库名;

创建表:  create table

create table stu(id int primary key, no int, age int, name char(10));
id int primary key代表id为int型是主键，值必须唯一;name char(10)名字10个字符


删除数据库： drop database 数据库名;
删除整张表: drop table stu;


增加：  insert into    values
insert into stu(id, no, age, name)values(4, 23, 23, '小明');
insert into stu(id, no,  name)values(5, 23, '小明');
insert into stu(id, no, age, name)values(4, 23, 23, '小明');
 
查询:  select  from  where
mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  1 |  2 |    2 | dfsd   |
|  2 |  2 |    2 | df     |
|  3 |  3 |    2 | 34fdsf |
|  4 | 23 |   23 | 小明   |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
 
mysql> select id from stu;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
 


mysql> select id, no, name,  age  from stu;
+----+----+--------+------+
| id | no | name   | age  |
+----+----+--------+------+
|  1 |  2 | dfsd   |    2 |
|  2 |  2 | df     |    2 |
|  3 |  3 | 34fdsf |    2 |
|  4 | 23 | 小明   |   23 |
|  5 | 23 | 小明   | NULL |
+----+----+--------+------+
 


mysql> select no, name from stu;
+----+--------+
| no | name   |
+----+--------+
|  2 | dfsd   |
|  2 | df     |
|  3 | 34fdsf |
| 23 | 小明   |
| 23 | 小明   |
+----+--------+
 
 
mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  1 |  2 |    2 | dfsd   |
|  2 |  2 |    2 | df     |
|  3 |  3 |    2 | 34fdsf |
|  4 | 23 |   23 | 小明   |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
 


mysql> select * from stu where age=23;
+----+----+-----+------+
| id | no | age | name |
+----+----+-----+------+
|  4 | 23 |  23 | 小明 |
+----+----+-----+------+
 


mysql> select id, name from stu where id>3;
+----+------+
| id | name |
+----+------+
|  4 | 小明 |
|  5 | 小明 |
+----+------+
 


mysql> select * from stu where age=23;
+----+----+-----+------+
| id | no | age | name |
+----+----+-----+------+
|  4 | 23 |  23 | 小明 |
+----+----+-----+------+
 
 


mysql> select id, name from stu where id>3;
+----+------+
| id | name |
+----+------+
|  4 | 小明 |
|  5 | 小明 |
+----+------+
   
mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  1 |  2 |    2 | dfsd   |
|  2 |  2 |    2 | df     |
|  3 |  3 |    2 | 34fdsf |
|  4 | 23 |   23 | 小明   |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
 
删除：   delete from   where
mysql> delete from stu where id=4;
 


mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  1 |  2 |    2 | dfsd   |
|  2 |  2 |    2 | df     |
|  3 |  3 |    2 | 34fdsf |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
 
mysql> delete from stu where id<3;
Query OK, 2 rows affected


mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  3 |  3 |    2 | 34fdsf |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
2 rows in set


删除：
mysql> delete from stu where id=3 and id=5;
Query OK, 0 rows affected


mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  3 |  3 |    2 | 34fdsf |
|  5 | 23 | NULL | 小明   |
+----+----+------+--------+
 
修改  update set  where
mysql> update stu set age =22 where id=5;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from stu;
+----+----+-----+--------+
| id | no | age | name   |
+----+----+-----+--------+
|  3 |  3 |   2 | 34fdsf |
|  5 | 23 |  22 | 小明   |
+----+----+-----+--------+
2 rows in set


mysql> update stu set age =2222;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0


mysql> select * from stu;
+----+----+------+--------+
| id | no | age  | name   |
+----+----+------+--------+
|  3 |  3 | 2222 | 34fdsf |
|  5 | 23 | 2222 | 小明   |
+----+----+------+--------+
2 rows in set


排序： 
mysql> select * from student
    -> order by age;
+----+------+------+------+
| id | no   | age  | name |
+----+------+------+------+
|  2 | 2000 |   22 | jom  |
|  1 | 2000 |   24 | tom  |
+----+------+------+------+
2 rows in set (0.00 sec)


添加列：alter table 表名 add 列名 列数据类型 [after 插入位置];
在表的最后追加列 address: alter table students add address char(60);
在名为 age 的列后插入列 birthday: alter table students add birthday date after age;
mysql> select * from student;
+----+------+------+----------+------+---------+
| id | no   | age  | birthday | name | address |
+----+------+------+----------+------+---------+
|  1 | 2000 |   24 | NULL     | tom  | NULL    |
|  2 | 2000 |   22 | NULL     | jom  | NULL    |
+----+------+------+----------+------+---------+
2 rows in set (0.00 sec)


修改列：alter table 表名 change 列名称 列新名称 新数据类型;
将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";
将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;


删除列：alter table 表名 drop 列名称;
删除 birthday 列: alter table students drop birthday;


重命名表名： alter table 表名 rename 新表名;