心得:
继续加油,数据库我来了~
1.库的操作:
#库操作
select user();
create database db1 charset utf8;
alter database db1 charset gbk;
show databases;
show create database db1;
drop database db1;![在这里插入图片描述](https://img-blog.csdnimg.cn/20191213001836773.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTAzNzAw,size_16,color_FFFFFF,t_70)
#################################################
mysql> select user(); #查看进入的当前用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> create database db1 charset utf8; #创建数据库并设置数据类型
Query OK, 1 row affected (0.00 sec)
mysql> alter database db1 charset gbk; #更改表的数据类型
Query OK, 1 row affected (0.00 sec)
mysql> show databases; #查询所有的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show create database db1; #查看已经创建数据库的信息
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop database db1; #删除数据库
Query OK, 0 rows affected (0.00 sec)
mysql> show databses; #已经不存在此数据库
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1
mysql>
2.表的操作
2.1表结构操作
#表结构操作
create database db1 charset utf8;
use db1;
create table t1(id int,name char(5));
desc t1;
alter table t1 rename t2;
desc t2;
alter table t2 modify name char(10);
desc t2;
alter table t2 change name NAME char(15);
desc t2;
show tables;
show create table t2;
select * from t2;
drop table t2;
######################################################
mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use db1; #使用数据库db1
Database changed
mysql> create table t1(id int,name char(5)); #创建表
Query OK, 0 rows affected (0.04 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t1 rename t2; #修改表名
Query OK, 0 rows affected (0.01 sec)
mysql> desc t2; #查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t2 modify name char(10); #修改的字段类型
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t2 change name NAME char(15); #修改字段名字和字符类型
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)
mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`NAME` char(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t2; #查看记录
Empty set (0.00 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.02 sec)
mysql>
2.2表内字段的操作
create table t1(id int, name char(5));
desc t1;
alter table t1 add age int;
desc t1;
alter table t1 drop age;
desc t1;
alter table t1 change name NAME char(10);
desc t1;
####################################################
mysql> create table t1(id int, name char(5));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t1 add age int;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t1 drop age;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t1 change name NAME char(10);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify NAME char(15);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
2.3表的复制
create table t2 like t1; #复制表结构
create table t3 select * from t1; #复制表内容和结构
create table t4 select * from t1 where 1>2; #复制表结构
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| id | NAME |
+------+------+
| 1 | tian |
| 2 | jian |
+------+------+
2 rows in set (0.00 sec)
mysql> create table t2 like t1;
Query OK, 0 rows affected (0.03 sec)
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
Empty set (0.00 sec)
mysql>
mysql> create table t3 select * from t1;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t3;
+------+------+
| id | NAME |
+------+------+
| 1 | tian |
| 2 | jian |
+------+------+
2 rows in set (0.00 sec)
mysql>
3.表内记录的操作
create table t1(id int,name char(5));
desc t1;
insert into t1 values(1,"tian"),(2,"jian"); #插入记录
select * from t1;
insert into t1 set id=3,name="li";#选择插入
insert into t1(id) values(4);#选择插入
select * from t1;
delete from t1 where id=4;#条件删除
select * from t1;
update t1 set name="ying" where id=3; #修改数据
select * from t1;
truncate t1; #删除表所有记录
#########################################################
mysql> create table t1(id int,name char(5));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t1 values(1,"tian"),(2,"jian");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tian |
| 2 | jian |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into t1 set id=3,name="li";
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id) values(4);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tian |
| 2 | jian |
| 3 | li |
| 4 | NULL |
+------+------+
4 rows in set (0.00 sec)
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tian |
| 2 | jian |
| 3 | li |
+------+------+
3 rows in set (0.00 sec)
mysql> update t1 set name="ying" where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tian |
| 2 | jian |
| 3 | ying |
+------+------+
3 rows in set (0.00 sec)
mysql> truncate t1;
Query OK, 0 rows affected (0.04 sec)
mysql>
4.数据类型
create table t1(
id int,
unsigned_number int unsigned,
point_number float,
name char(10),
var_name varchar(10),
birthday datetime
);
#################################################
mysql> create table t1(
-> id int,
-> unsigned_number int unsigned,
-> point_number float,
-> name char(10),
-> var_name varchar(10),
-> birthday datetime
->
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> desc t1;
+-----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| unsigned_number | int(10) unsigned | YES | | NULL | |
| point_number | float | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| var_name | varchar(10) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+-----------------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> insert into t1 values(-1,1,3.1415,"tian","jian","2020-11-14 12:12:12");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from t1;
+------+-----------------+--------------+------+----------+---------------------+
| id | unsigned_number | point_number | name | var_name | birthday |
+------+-----------------+--------------+------+----------+---------------------+
| -1 | 1 | 3.1415 | tian | jian | 2020-11-14 12:12:12 |
+------+-----------------+--------------+------+----------+---------------------+
1 row in set (0.00 sec)
mysql>
5.约束
#约束
create table t2(
id int primary key auto_increment,
name char(10) not null,
age int default 18,
sex enum("male","female"),
hobby set("readding","running","swimming","eatting"),
ip char(15),
port char(4),
unique(ip,port)
);
desc t2;
insert into t2 values(null,"tian",26,"male","swimming,readding","192.168.1.1","8080");
select * from t2;
####################################################
mysql> #约束
mysql> create table t2(
-> id int primary key auto_increment,
-> name char(10) not null,
-> age int default 18,
-> sex enum("male","female"),
-> hobby set("readding","running","swimming","eatting"), #集合顺序可以
-> ip char(15),
-> port char(4),
-> unique(ip,port)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc t2;
+-------+------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | 18 | |
| sex | enum('male','female') | YES | | NULL | |
| hobby | set('readding','running','swimming','eatting') | YES | | NULL | |
| ip | char(15) | YES | MUL | NULL | |
| port | char(4) | YES | | NULL | |
+-------+------------------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> insert into t2 values(null,"tian",26,"male","swimming,readding","192.168.1.1","8080");
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+----+------+------+------+-------------------+-------------+------+
| id | name | age | sex | hobby | ip | port |
+----+------+------+------+-------------------+-------------+------+
| 1 | tian | 26 | male | readding,swimming | 192.168.1.1 | 8080 |
+----+------+------+------+-------------------+-------------+------+
1 row in set (0.00 sec)
mysql>
外键
#1建立被关联的表
create table dep(
id int primary key,
name char(16),
comment char(50)
);
desc dep;
#2建立关联的表
create table person(
id int primary key,
name char(12),
sex enum("male","female"),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
desc person;
insert into dep values(1,"IT","jishu"),(2,"HR","zahuo");
insert into person values(1,"tian","male",1),(2,"jian","male",1),(3,"ying","female",2);
select * from dep;
select * from person;
update dep set id=3 where id =1;
select * from dep;
select * from person;
#############################################################
mysql> #1建立被关联的表
mysql> create table dep(
-> id int primary key,
-> name char(16),
-> comment char(50)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc dep;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(16) | YES | | NULL | |
| comment | char(50) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> #2建立关联的表
mysql> create table person(
-> id int primary key,
-> name char(12),
-> sex enum("male","female"),
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> on delete cascade
-> on update cascade
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc person;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(12) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| dep_id | int(11) | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> insert into dep values(1,"IT","jishu"),(2,"HR","zahuo");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into person values(1,"tian","male",1),(2,"jian","male",1),(3,"ying","female",2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dep;
+----+------+---------+
| id | name | comment |
+----+------+---------+
| 1 | IT | jishu |
| 2 | HR | zahuo |
+----+------+---------+
2 rows in set (0.00 sec)
mysql> select * from person;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 1 | tian | male | 1 |
| 2 | jian | male | 1 |
| 3 | ying | female | 2 |
+----+------+--------+--------+
3 rows in set (0.00 sec)
mysql> update dep set id=3 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+----+------+---------+
| id | name | comment |
+----+------+---------+
| 2 | HR | zahuo |
| 3 | IT | jishu |
+----+------+---------+
2 rows in set (0.01 sec)
mysql> select * from person;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 1 | tian | male | 3 |
| 2 | jian | male | 3 |
| 3 | ying | female | 2 |
+----+------+--------+--------+
3 rows in set (0.00 sec)
mysql>