MySQL(day46-day48)
1,SQL 语句要求:
mySqld.exe 服务端
mySql.exe 客户端
2,数据库操作(DDL)
-1) 创建数据库(即:在磁盘上创建一个对应的文件夹)
creat database 数据库名;
creat database if not exists 数据库名 character set xxx 代表创建数据库并且设置数据库格式例如:utf8
例如:
mysql> create database s3;
Query OK, 1 row affected (0.00 sec)
creat database if not exists 数据库名;===》此处代表创建数据库,如果存在相同的数据库名,则pass,不创建;不存在,则创建
例如:
mysql> create database if not exists s3;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;====》查看警告内容
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1007 | Can't create database 's3'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
creat database if not exists 数据库名 character set utf8 设置创建的数据库格式是utf8.
-2)查看数据库:
show databases; (查看所有数据库)
show creat database db_name (查看某个数据库在创建时的数据信息)
例如:
mysql> create database if not exists s43 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database s43;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| s43 | CREATE DATABASE `s43` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
-3)修改数据库:alter databases db_name
alter database 数据库名 character set utf8 ===》代表修改数据库的格式为utf8
mysql> alter database s3 character set utf8; ==>修改数据库s3为utf8格式
Query OK, 1 row affected (0.00 sec)
mysql> show create database s3; ==》查看数据库的创建信息
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| s3 | CREATE DATABASE `s3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
-4)删除数据库
drop dabase 数据库名;
例如:
mysql> drop database wang;
Query OK, 0 rows affected (0.01 sec)==》代表mysql 执行成功
-5)使用数据库
切换数据库:use db_name; 注意:进入某个数据库后没有办法再退回之前状态,但可以通过use 进行切换
查看当前使用的数据库:select database();
例如:
mysql> use s43; ====进入使用数据库s43
Database changed
mysql> select database();===查看当前使用的数据库
+------------+
| database() |
+------------+
| s43 |
+------------+
1 row in set (0.00 sec)
3)数据库中内容的类型
数值类型
字符串类型
时间数据类型
4)关于表的操作
约束:
primary key (非空且唯一):能够唯一区分出当前记录的字段称为主键。:unique,not null
auto_increment(自动增加):主字段必须是数字类型
外键约束 foreign key */
-1,创建表(类似一个excel 表)
create table 表名(
field1 type[完整约束条件],
field2 type,
...
fieldn type
)[character set xxx];
例如:
mysql> create TABLE employee(
-> id TINYINT PRIMARY KEY auto_increment,====》代表id 类型是tinyint是主键,内容自动增加
-> name VARCHAR(25),===>代表 name 类型是varchar,长度25
-> gender TINYINT(1),
-> age INT ,===>age 类型整数
-> department VARCHAR(20),
-> salary DOUBLE(7,2)===》代表salary 类型是double,长度7,小数点2位数
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+---------------+
| Tables_in_s43 |
+---------------+
| employee |
+---------------+
1 row in set (0.00 sec)
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
-2,查看表信息
查看表结构:desc 表名
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
查看表结构:show columns from 表名
查看当前数据库中所有的表:show tables
mysql> show tables;
+---------------+
| Tables_in_s43 |
+---------------+
| employee |
+---------------+
1 row in set (0.00 sec)
查看当前数据库表建表语句: show create table 表名
-3 修改表结构
--(1) 增加列(字段)
alter table 表名 add 增加内容 内容类型 限制条件
mysql> alter table employee add entry_date date not null;;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
| entry_date | date | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
--增加多个字断
alter table 表名 add 增加内容 类型,
add 增加内容 类型;
mysql> alter table employee add A INT,
-> ADD B INT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
| entry_date | date | NO | | NULL | |
| A | int(11) | YES | | NULL | |
| B | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
--删除列
---删除一列:alter table 表名 drop 删除内容
例如:
mysql> alter table employee DROP entry_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
---删除多列:alter table 表名
drop 删除内容 ,
drop 删除内容 ;
例如:
mysql> alter table employee DROP A,
-> DROP B;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
--修改类型
alter table 表名 modify 列名 需要修改成的类型 first/after ===>其中first/after 代表此列放置的位置
例如:
mysql> alter table employee modify age SMALLINT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> alter table employee modify age smallint after id;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| age | smallint(6) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| department | varchar(20) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
--修改列名
alter table 表名 列名 新列名 类型;
例如:
mysql> alter table employee change department depart varchar(20)after salary;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| age | smallint(6) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| depart | varchar(20) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
--修改表名
rename table 表名 to 新表名
mysql> rename table employee to emp;
Query OK, 0 rows affected (0.01 sec)
mysql> desc emp;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
| age | smallint(6) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| gender | tinyint(1) | YES | | NULL | |
| salary | double(7,2) | YES | | NULL | |
| depart | varchar(20) | YES | | NULL | |
| is_married | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
--删除表
drop table 表名
---添加主键,删除主键
alter table 表名 add primary key
alter table 表名 drop primary key
--表记录 增删改查
--增加一条记录insert
insert into 表名(列1,列2,列3)values(值1,值2,值3)
例如:
mysql> insert into emp (id,age,name,gender,salary,depart,is_married) values (1,38,'alex',0,1700,'技术部',1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from emp;
+----+------+------+--------+---------+-----------+------------+
| id | age | name | gender | salary | depart | is_married |
+----+------+------+--------+---------+-----------+------------+
| 1 | 38 | alex | 0 | 1700.00 | 技术部 | 1 |
+----+------+------+--------+---------+-----------+------------+
1 row in set (0.00 sec