SQL语句:
DDL语句::数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言
1.CREATE - to create objects in the database 创建
2.ALTER - alters the structure of the database 修改
3.DROP - delete objects from the database 删除
1)登陆连接mysql -u root -p 回车:输入密码;或输入 mysql -u root -p+密码;
输入要求:各单词间 中间一定要用空格,符号一定要用英文的!切记!!!
如下:
```
mysql -u root -p #回车
#输入密码
```
2)show databases ---->查看数据库;
如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| day01 |
| mysql |
| performance_schema |
| test |
+--------------------+
3)create database 数据库的名字-----------》创建一个(数据库名字)数据库;
如下:
```
create database day01 # 创建一个数据库名字为day01的数据库
```
4)use 数据库的名字--------》使用数据库;备注:创建表格前一定要先确定使用那个数据库!
如下:
```
use day01 #使用数据库day01;
```
5)create table 表格名字(字段1 数据类型,字段 2数据类型,字段3 数据类型,字段4 数据类型。。。。)-----》创建一个表格;
数据类型:
1 整数类型:
整数类型的取值范围:
2浮点数类型和定点数类型:
2.1解释M,D的意思:
M:数值的总位数。 通俗点讲,就是看有多少个数字,比如,5.6789,M就是5
D:小数点后面能保留几位。 比如上面的5.6789 ,D就是4。 这只是举一个例子,来说明M,D是什么,实际是先有M,D的,然后在来控制数值,而不是更具数值来确定M,D。
不单单就MECIMAL有M,D这两个参数,FLOAT 和 DOUBLE 度有,看下面例子
比如:
CREATE TABLE tmp(
x FLOAT(3,1),
y DOUBLE(5,3),
z DECIMAL(5,4)
);
假设x插入的值为:5.69,56.78,5.438,349.2 (注意:实验给x这个字段插入的值,可能实验了三次,不要错看成x的值为5.69,y为56.78等等了)
实际上在数据库中存的值为:5.7,56.9,5.3,349.2这个报错
分析:x的M为3,D为1,那么小数点上必须是占了一位数字,就算没有值,也会用0来填充,所以说,整数位上最多就只能是2位,这里要切记要先根据D的值,来算整数位能最多有多少位。
通过分析x,y和z也就简单了,
y字段上的值,整数部分最多是2位,小数点后的位数最多是3位,也就是说小数点后超过了3位,就会四舍五入。
z字段上的值,整数部分最多只能是一位,小数点后的位数最多是4位,如果不足4位,也会用0补充。比如插入1.56,在数据库中存的就是1.5600, 比如插入25.46,这个就会报错,因为整数部分只能是一位,小数点后的位数已经占了4位了。这里要搞清楚。
2.2、FLOAT、DOUBLE、DECIMAL三者的区别。
都是用来表示我们所说的小数的也就是浮点数,但是三种的精度不一样,也就是后面显示的位数不一样,
区别一:
FLOAT显示后面的小数点位大概在40多位,
DOUBLE能显示的就是300多位了,不是一个层次上的,
DECIMAL这个小数点后面能显示的位数跟DOUBLE差不多,
区别二:
FLOAT和DOUBLE在不指定精度时,也就是不用(M,D),默认会按照实际的精度,也就是你写多少就是多少,而DECIMAL如不指定精度默认为(10,0),也就是如果不指定精度,插入数值56.89,在数据库中存储的就是57。所以一般使用DECIMAL时就会指定精度,而使用FLOAT和DOUBLE就不用。
区别三:
浮点数相对与定点数(DECIMAL)的优点就是在长度一定的情况下,浮点数能够表示更大的数据范围,但是缺点是会引起精度问题。
2.3、什么时候使用FLOAT、DOUBLE、DECIMAL
对精度要求比较高的时候,比如货币、科学数据等,使用DECIMAL的类型比较好。其他的时候,看你要存放的数据的大小而定了,一般使用DOUBLE。并且在使用浮点数时需要注意,尽量避免做浮点数的比较,比如加、减,谁大谁小,这样的操作,会引起精度缺失。相信在一些程序语言中,遇到过float精度丢失的问题。
3日期和时间类型:
6)show create database 数据库名称---------》查看数据库 的创建细节;
如下:
mysql> show create database day01;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| day01 | CREATE DATABASE `day01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
7)create database 数据库名字 character set gbk---------------》创建一个字符集为gbk格式的数据库;或create database 数据库名字 charset=gbk;
如下:
mysql> create database day001 character set gbk;
Query OK, 1 row affected (0.02 sec)
8)drop database 数据库名字-----------------------》删除数据库;
如下:
mysql> drop database day001;
Query OK, 0 rows affected (0.01 sec)
9)select database():-------------------------》查询正在使用那一个数据库;
如下:
mysql> select database();
+------------+
| database() |
+------------+
| day01 |
+------------+
1 row in set (0.00 sec)
10)desc 表格名:------------------》查看表格结构;
如下:
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(10) | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
11)show create table 表格名:--------------》查看表格的创建细节;
如下:
mysql> show create table employee;
+----------+-----------------------------------------------
-----------------------------------------------------------
-------------------------------------------------+
| Table | Create Table
|
+----------+-----------------------------------------------
-----------------------------------------------------------
-------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`gender` char(10) DEFAULT NULL,
`salary` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------
-----------------------------------------------------------
-------------------------------------------------+
1 row in set (0.00 sec)
12)alter table 表格名 add 字段 数据类型:---------------》增加一个字段;
如下:
mysql> alter table employee add job varchar(10);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(10) | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
| job | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
13)alter table 表格名 modify 字段 数据类型:-----------------》修改字段的数据类型;
如下:
mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
mysql> alter table employee modify name char(10);
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc employee;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| gender | char(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
14)alter table 表格名 change (旧的字段名) (新的字段名) 数据类型:---------------》修改字段名;
如下:
mysql> desc employee;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| gender | char(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
mysql> alter table employrr change gender sex char(5);
ERROR 1146 (42S02): Table 'day01.employrr' doesn't exist
mysql> alter table employee change gender sex char(5);
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc employee;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
15)alter table 表格名 drop 字段:--------》删除字段;
如下:
mysql> desc employee;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
5 rows in set (0.03 sec)
mysql> alter table employee drop birthday;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
16)alter table 表格名 character set utf8(gbk):---------------->修改字符集;或alter table 表格名 charset=utf8(gbk):如下:
mysql> show create table employee;
+----------+-------------------------------------
-------------------------------------------------
------------------------------------------+
| Table | Create Table
|
+----------+-------------------------------------
-------------------------------------------------
------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`sex` char(5) DEFAULT NULL,
`salary` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------
-------------------------------------------------
------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table employee character set gbk;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table employee;
+----------+-------------------------------------
-------------------------------------------------
-------------------------------------------------
| Table | Create Table
+----------+-------------------------------------
-------------------------------------------------
-------------------------------------------------
| employee | CREATE TABLE `employee` (
`id` int(11) DEFAULT NULL,
`name` char(10) CHARACTER SET utf8 DEFAULT NULL
`sex` char(5) CHARACTER SET utf8 DEFAULT NULL,
`salary` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-------------------------------------
-------------------------------------------------
-------------------------------------------------
1 row in set (0.00 sec)
mysql> alter table employee charset=utf8;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table employee;
+----------+-------------------------------------
-------------------------------------------------
------------------------------------------+
| Table | Create Table
|
+----------+-------------------------------------
-------------------------------------------------
------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`sex` char(5) DEFAULT NULL,
`salary` float(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
17)alter table 表格名 rename to 新的表格名;或 rename table 旧的表格名 to 新的表格名:------》修改表格名;
如下:
mysql> alter table employee rename to user;
Query OK, 0 rows affected (0.02 sec)
mysql> desc user;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> rename table user to employee;
Query OK, 0 rows affected (0.02 sec)
mysql> desc employee;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| salary | float(7,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
18)drop table 表格名:-------》删除表格;
如下:
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| employee |
+-----------------+
1 row in set (0.00 sec)
mysql> drop table employee;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
19)drop database 数据库名:------------------》删除数据库;注意:软件自带的数据库不能删,删除的话就无法工作啦,如:information-schema,mysql,performance-schema;test;
如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| day01 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database day01;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
DML语句:数据操作语言,对表格中的数据进行操作。有:insert,update,delete。。。。
1 insert 插入语句:
1)全字段:
insert into 表格名(字段1,字段2,字段3,字段4.。。。) values(值1,值2,值3,值4.。。。);如下:
mysql> select * from employee;
Empty set (0.00 sec)
mysql> insert into employee(id,name,gender,birthday,salary) values(1,'zs','man',
'1990-02-23',12345.32);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+------+------+--------+------------+----------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+----------+
| 1 | zs | man | 1990-02-23 | 12345.32 |
+------+------+--------+------------+----------+
1 row in set (0.00 sec)
2)字段全省略:
insert into 表格名 values(值1,值2,值3,值4.。。。);
如下:
mysql> insert into employee values(2,'ls','woman','1990-03-13',12745.82);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+------+------+--------+------------+----------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+----------+
| 1 | zs | man | 1990-02-23 | 12345.32 |
| 2 | ls | woman | 1990-03-13 | 12745.82 |
+------+------+--------+------------+----------+
2 rows in set (0.00 sec)
mysql>
3)带部分字段,注意的是,值一定要一一对应:
insert into 表格名(字段1,字段3,字段5.。。。) values(值1,值3,值5.。。。);字段值默认为null,如下:
mysql> insert into employee(id,gender,salary) values(1,'man',2345.88);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+------+------+--------+------------+----------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+----------+
| 1 | zs | man | 1990-02-23 | 12345.32 |
| 2 | ls | woman | 1990-03-13 | 12745.82 |
| 1 | NULL | man | NULL | 2345.88 |
+------+------+--------+------------+----------+
3 rows in set (0.00 sec)
mysql>
2 update 语句:
修改、更新,用来修改表格中记录的数据;
语法:update 表格名 set 字段名=值(这里修改的是表格中锁有的这个字段);如下:
mysql> select * from employee;
+------+------+--------+------------+----------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+----------+
| 1 | zs | man | 1990-02-23 | 12345.32 |
| 2 | ls | woman | 1990-03-13 | 12745.82 |
| 1 | NULL | man | NULL | 2345.88 |
+------+------+--------+------------+----------+
mysql> update employee set salary=8000;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from employee;
+------+------+--------+------------+---------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+---------+
| 1 | zs | man | 1990-02-23 | 8000.00 |
| 2 | ls | woman | 1990-03-13 | 8000.00 |
| 1 | NULL | man | NULL | 8000.00 |
+------+------+--------+------------+---------+
3 rows in set (0.00 sec)
update 表格名 set 字段名=值 where 字段=‘’(这里修改的是表格中指定字段行中字段名地值);如下:
mysql> select * from employee;
+------+------+--------+------------+---------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+---------+
| 1 | zs | man | 1990-02-23 | 8000.00 |
| 2 | ls | woman | 1990-03-13 | 8000.00 |
| 1 | NULL | man | NULL | 8000.00 |
+------+------+--------+------------+---------+
3 rows in set (0.00 sec)
mysql> update employee set salary=9500 where name='zs';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+------+------+--------+------------+---------+
| id | name | gender | birthday | salary |
+------+------+--------+------------+---------+
| 1 | zs | man | 1990-02-23 | 9500.00 |
| 2 | ls | woman | 1990-03-13 | 8000.00 |
| 1 | NULL | man | NULL | 8000.00 |
+------+------+--------+------------+---------+
3 rows in set (0.00 sec)