MySQL篇,第一章:数据库知识1

MySQL 数据库 1

一、MySQL概述
1、什么是数据库
       数据库是一个存储数据的仓库


2、哪些公司在用数据库
       金融机构、购物网站、游戏网站、论坛网站... ...


3、提供数据库服务的软件
1、软件分类:
        MySQL、SQL_Server、Oracle、DB2、Mariadb、MongoDB ..
2、在生产环境中,如何选择使用哪个数据库软件
1、是否开源
1、开源软件
MySQL、Mariadb、MongoDB
2、商业软件
Oracle、DB2、SQL_Server
2、是否跨平台
      1、不跨平台 :SQL_Server
      2、跨平台
MySQL、Oracle、DB2、Mariadb、MongoDB
3、公司类型
1、商业软件:政府部门、金融机构
2、开源软件:游戏网站、购物网站、论坛网站...


4、MySQL特点
1、关系型数据库
       1、关系型数据库特点
             1、数据是以行和列的形式存储的
             2、这一系列的行和列成为表
             3、表中的每一行叫一条记录
             4、表中的每一列叫一个字段
             5、表和表之间的逻辑关联叫关系
             6、关系型数据库的核心内容是 关系 即 二维表
2、示例
     1、关系型数据库存储
表1、学生信息表
姓名 年龄 班级
张三丰 25 AID1712
金花婆婆 26 AID1711

表2、班级信息表
班级 班主任
AID1712 侯大大
AID1711 孙大大
2、非关系型数据库存储
{姓名:"张三丰",年龄:25,班级:"1712",班主任:"侯"}
{姓名:"张三丰",年龄:25,班级:"1712",班主任:"侯"}
2、跨平台
可以在Unix、Linux、Windows上运行MySQL服务
3、支持多种编程语言
Python、java、php、... ...


二、MySQL安装
1、Ubuntu安装MySQL服务
  1、安装服务端
  sudo apt-get install mysql-server
  2、安装客户端
  sudo apt-get install mysql-client
2、Windows安装MySQL服务
  1、下载MySQL安装包(Windows)
    mysql-install-**5.7**.msi
  2、双击、按照教程安装即可;

 

三、启动和连接Mysql服务
1、服务端启动
  1、查看Mysql服务的状态
    sudo /etc/init.d/mysql status
  2、启动Mysql服务
    sudo /etc/init.d/mysql start
  3、停止Mysql服务
    sudo /etc/init.d/mysql stop
  4、重启Mysql服务
    sudo /etc/init.d/mysql restart
2、客户端连接
  1、命令格式
    mysql -h主机名 -u用户名 -p密码
    mysql -hlocalhost -uroot -p123456
  2、本地连接可以省略 -h 选项
    mysql -uroot -p123456
  3、断开与服务器的连接
    exit | quit | \q

  3,远程连接

  (1)进入数据库,进行数据库设置;

  >use mysql;

  >select  host,user  from user;

+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+

 > update user set host='%' where user='root' AND host='localhost'; 

  > select host,user from user;
+-------------+------+
| host | user |
+-------------+------+
| % | root |
| 127.0.0.1 | root |
| 192.168.1.% | rep |
| ::1 | root |
+-------------+------+

>FLUSH PRIVILEGES;

  (2)远程机器连接:#mysql   -h  192.168.1.110  -P 3306  -u root -p

 

四、基本SQL命令
1、SQL命令的使用规则
  1、每条命令必须以分号 ; 结尾
  2、SQL命令不区分字母大小写
  3、使用 \c 终止命令的执行;  
2、库的管理
  1、库的基本操作
    1、查看已有的库
      show databases;
    2、创建库(指定字符集)
      create database 库名 default charset=utf8;
    3、查看创建库的语句
      show create database 库名;
    4、查看当前所在库
      select database();
    5、切换库
      use 库名;
    6、查看库中已有表
      show tables;
    7、删除库
      drop database 库名;

2、库的命名规则
  1、可以使用数字、字母、_,但是不能是纯数字
  2、库名区分字母大小写
  3、库名具有唯一性
  4、不能使用特殊字符和mysql关键字
3、练习
  1、创建库AID1712db,指定字符集为utf8
  2、进入到库AID1712db中
  3、查看当前所在库
  4、查看库中已有表
  5、查看AID1712db的字符集
  6、删除库AID1712db
3、表的管理
 1、表的基本操作
  1、创建表
    create table 表名(
      字段名 数据类型,
      字段名 数据类型,
        ...
      );
  2、查看创建表的语句(字符集)
    show create table 表名;
  3、查看表结构
    desc 表名;
  4、删除表
    drop table 表名;

  5,查看所有的表;

    show tables;
   2、注意
  1、所有的数据都是以文件的形式存储在数据库目录下
  2、数据库目录:/var/lib/mysql
3、练习
  1、创建库python
  2、在python库中创建表py_mysql,字段有如下三个
    id kuname biaoname 数据类型自己定义
  3、查看创建表的语句
  4、查看py_mysql的表结构
  5、删除表py_mysql

 

4、表记录的管理
  1、在表中插入记录
    1、insert into 表名 values(值1),(值2),....;
  2、查看表记录
    1、select * from 表名;
    2、select 字段名1,字段名2,... from 表名;
  3、练习
    1、查看所有的库
    2、创建一个新库studb
    3、在studb中创建一张表t1,字段有4个
      id name age score 数据类型自己定义
    4、查看t1的表结构
    5、在表t1中随便插入两条记录
    6、查看t1表中的所有记录
    7、查看创建表t1的语句(字符集)
5、如何更改默认字符集
  1、方法
    通过更改Mysql的配置文件实现
  2、步骤
    1、获取root权限
      sudo -i
    2、修改mysql配置文件
      vi /etc/mysql/mysql.conf.d/mysqld.cnf
      [mysqld]
      character_set_server = utf8
    3、重启mysql服务
      sudo /etc/init.d/mysql restart
6、客户端把数据存储到数据库服务器上的过程
  1、连接到数据库服务器 : mysql -uroot -p
  2、选择库 : use 库名;
  3、创建/修改表
  4、断开与数据库的连接 :exit | quit | \q
7、数据类型
  1、数值类型(有符号signed 和 无符号unsigned)
    1、整型
      1、int 大整型(4个字节)
        取值范围:0~2**32 -1
      2、tinyint 微小整型(1个字节)
        1、有符号(signed默认) -128~127
        2、无符号(unsigned) 0~255
      3、smallint 小整型(2个字节)
        取值范围:0~65535
      4、bigint 极大整型(8个字节)
        取值范围:0~2**64 -1
    2、浮点型
      1、float(4个字节,最多显示7个有效位)
        1、用法
          字段名 float(m,n) m->总位数 n->小数位位数
          float(5,2) 取值范围:-999.99~999.99
        2、注意
          1、浮点型插入整数时会自动补全小数位数
          2、小数位如果多于指定的位数,会对下一位四舍五入
      2、double(8个字节,最多显示15个有效位)
        1、用法
          字段名 double(m,n)
      3、decimal(M+2个字节,最多显示28个有效位)
        1、用法
          decimal(M,D)
  2、字符类型
     1、char(定长)
        1、宽度取值范围:1~255
        2、不给定宽度时,默认宽度为1
     2、varchar(变长)
        1、取值范围:1~65535
        2、注意
             1、varchar没有默认宽度,必须给定一个宽度;
          2、char和varchar使用时都给定宽度,但不能超过各自的范围;
     3、char 和 varchar的特点
        1、char
         浪费存储空间,性能高;
        2、varchar
         节省存储空间,性能低(弹性扩展);
        4、字符类型的宽度和数值类型的宽度的区别
       1、数值类型的宽度为显示宽度,只用于select查询时使用,和占用存储空间大小无关,可用zerofill查看效果
       2、字符类型的宽度超过则无法存储
 3、练习:

1、创建表stuinfo1712,utf8,字段要求:
学号 : id 要求显示宽度为3,位数不够用0填充
姓名 : name 变长,宽度20
班级 : class 定长,宽度为7
年龄 :age 微小整型,不能输入负数
身高 :height 浮点型,小数位数2位
工资 :salary 浮点型,小数位2位,最大值99999.99

2、在表中插入两条记录
3、查询表中记录,只显示姓名、年龄和工资
select name,age,salary from stuinfo1712;
4、查看表结构

 

 1 mysql> create table t1(
 2     -> id int,
 3     -> name char(2),
 4     -> age tinyint unsigned
 5     -> )default charset=utf8;
 6 Query OK, 0 rows affected (0.39 sec)
 7 
 8 
 9 mysql> 
10 mysql> 
11 mysql> insert into t1 values(1,'西门庆',38);
12 ERROR 1406 (22001): Data too long for column 'name' at row 1
13 mysql> insert into t1 values(1,'金莲',38);
14 Query OK, 1 row affected (0.04 sec)
15 
16 mysql> desc t1;
17 +-------+---------------------+------+-----+---------+-------+
18 | Field | Type                | Null | Key | Default | Extra |
19 +-------+---------------------+------+-----+---------+-------+
20 | id    | int(11)             | YES  |     | NULL    |       |
21 | name  | char(2)             | YES  |     | NULL    |       |
22 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
23 +-------+---------------------+------+-----+---------+-------+
24 3 rows in set (0.10 sec)
25 
26 mysql> select * from t1;
27 +------+--------+------+
28 | id   | name   | age  |
29 +------+--------+------+
30 |    1 | 金莲   |   38 |
31 +------+--------+------+
32 1 row in set (0.00 sec)
33 
34 mysql> insert into t1 values(1,'aaa',18);
35 ERROR 1406 (22001): Data too long for column 'name' at row 1
36 mysql> insert into t1 values(1,'aa',18);
37 Query OK, 1 row affected (0.03 sec)
38 
39 mysql> select * from t1;
40 +------+--------+------+
41 | id   | name   | age  |
42 +------+--------+------+
43 |    1 | 金莲   |   38 |
44 |    1 | aa     |   18 |
45 +------+--------+------+
46 2 rows in set (0.00 sec)
47 
48 mysql> 
View Code

 

 1 mysql> create table t3( id int(3) zerofill, name char(15) )default charset=utf8; 
 2 Query OK, 0 rows affected (0.17 sec)
 3 
 4 mysql> insert into t3 values(1,'金毛狮王');
 5 Query OK, 1 row affected (0.08 sec)
 6 
 7 mysql> select * from t3;
 8 +------+--------------+
 9 | id   | name         |
10 +------+--------------+
11 |  001 | 金毛狮王     |
12 +------+--------------+
13 1 row in set (0.00 sec)
14 
15 mysql> create table t4(
16     -> id int(100) zerofill,
17     -> name varchar(15)
18     -> );
19 Query OK, 0 rows affected (0.12 sec)
20 
21 mysql> insert into t4 values(1,'zisanlongwang');
22 Query OK, 1 row affected (0.04 sec)
23 
24 mysql> select * from t4;
25 +------------------------------------------------------------------------------------------------------+---------------+
26 | id                                                                                                   | name          |
27 +------------------------------------------------------------------------------------------------------+---------------+
28 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 | zisanlongwang |
29 +------------------------------------------------------------------------------------------------------+---------------+
30 1 row in set (0.01 sec)
31 
32 mysql> 
33 ###
34 ctrl +s 是屏蔽终端输出,ctrl + q 显示终端输出
View Code

 

 1 mysql> use db1
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 mysql> create table stuinfo1806(
 7     -> id int(3) zerofill,
 8     -> name varchar(20),
 9     -> class char(7),
10     -> age tinyint unsigned,
11     -> height float(5,2),
12     -> salary decimal(7,2)
13     -> )default charset=utf8;
14 Query OK, 0 rows affected (0.36 sec)
15 
16 mysql> show tables;
17 +---------------+
18 | Tables_in_db1 |
19 +---------------+
20 | stuinfo       |
21 | stuinfo1806   |
22 | t1            |
23 | t2            |
24 | t3            |
25 | t4            |
26 +---------------+
27 6 rows in set (0.00 sec)
28 
29 mysql> insert into stuinfo1806 values(1,'xiaoming','AID1806',20,120.1234,10000.12345);
30 Query OK, 1 row affected, 1 warning (0.07 sec)
31 
32 mysql> select * from stuinfo1806;
33 +------+----------+---------+------+--------+----------+
34 | id   | name     | class   | age  | height | salary   |
35 +------+----------+---------+------+--------+----------+
36 |  001 | xiaoming | AID1806 |   20 | 120.12 | 10000.12 |
37 +------+----------+---------+------+--------+----------+
38 1 row in set (0.05 sec)
39 
40 mysql> insert into stuinfo1806 values(2,'lili','AID1807',18,100.87633,7000.2334);
41 Query OK, 1 row affected, 1 warning (0.05 sec)
42 
43 mysql> select * from stuinfo1806;
44 +------+----------+---------+------+--------+----------+
45 | id   | name     | class   | age  | height | salary   |
46 +------+----------+---------+------+--------+----------+
47 |  001 | xiaoming | AID1806 |   20 | 120.12 | 10000.12 |
48 |  002 | lili     | AID1807 |   18 | 100.88 |  7000.23 |
49 +------+----------+---------+------+--------+----------+
50 2 rows in set (0.00 sec)
51 
52 mysql> desc stuinfo1806;
53 +--------+--------------------------+------+-----+---------+-------+
54 | Field  | Type                     | Null | Key | Default | Extra |
55 +--------+--------------------------+------+-----+---------+-------+
56 | id     | int(3) unsigned zerofill | YES  |     | NULL    |       |
57 | name   | varchar(20)              | YES  |     | NULL    |       |
58 | class  | char(7)                  | YES  |     | NULL    |       |
59 | age    | tinyint(3) unsigned      | YES  |     | NULL    |       |
60 | height | float(5,2)               | YES  |     | NULL    |       |
61 | salary | decimal(7,2)             | YES  |     | NULL    |       |
62 +--------+--------------------------+------+-----+---------+-------+
63 6 rows in set (0.00 sec)
64 
65 mysql> show create table stuinfo1806;
66 +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
67 | Table       | Create Table                                                                                                                                                                                                                                                                                          |
68 +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
69 | stuinfo1806 | CREATE TABLE `stuinfo1806` (
70   `id` int(3) unsigned zerofill DEFAULT NULL,
71   `name` varchar(20) DEFAULT NULL,
72   `class` char(7) DEFAULT NULL,
73   `age` tinyint(3) unsigned DEFAULT NULL,
74   `height` float(5,2) DEFAULT NULL,
75   `salary` decimal(7,2) DEFAULT NULL
76 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
77 +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
78 1 row in set (0.06 sec)
79 
80 mysql> select name,age,salary from stuinfo1806;
81 +----------+------+----------+
82 | name     | age  | salary   |
83 +----------+------+----------+
84 | xiaoming |   20 | 10000.12 |
85 | lili     |   18 |  7000.23 |
86 +----------+------+----------+
87 2 rows in set (0.00 sec)
88 
89 mysql> 
View Code

 

    

 3、枚举类型
   1、定义 :字段值只能在列举的范围内选择
   2、enum 单选(最多有65535个不同的值)
      字段名 enum(值1,值2,...)
   3、set 多选(最多有64个不同的值)
      字段名 set(值1,值2,...)
      likes set("Study","Girl","Python","MySQL")
      "Study,Gril"

 1 mysql> create table t5(
 2     -> id int(3) zerofill,
 3     -> name varchar(15),
 4     -> class char(7),
 5     -> sex enum('','','保密'),
 6     -> likes set('','','学习','人工智能')
 7     -> )default charset=utf8;
 8 Query OK, 0 rows affected (0.65 sec)
 9 
10 mysql> select * from t5;
11 Empty set (0.02 sec)
12 
13 mysql> show tables;
14 +---------------+
15 | Tables_in_db1 |
16 +---------------+
17 | stuinfo       |
18 | stuinfo1806   |
19 | t1            |
20 | t2            |
21 | t3            |
22 | t4            |
23 | t5            |
24 +---------------+
25 7 rows in set (0.04 sec)
26 
27 mysql> insert into t5 values
28     -> (1,'情意父王','AID1806','','女,学习,人工智能');
29 ERROR 1265 (01000): Data truncated for column 'likes' at row 1
30 mysql> insert into t5 values (1,'情意父王','AID1806','','女, 学习 ,人  能');Query OK, 1 row affected (0.06 sec)
31 
32 mysql> select * from t5;
33 +------+--------------+---------+------+-------------------------+
34 | id   | name         | class   | sex  | likes                   |
35 +------+--------------+---------+------+-------------------------+
36 |  001 | 情意父王     | AID1806 || 女,学习,人工智能        |
37 +------+--------------+---------+------+-------------------------+
38 1 row in set (0.01 sec)
39 
40 mysql> select * from t5\G;
41 *************************** 1. row ***************************
42    id: 001
43  name: 情意父王
44 class: AID1806
45   sex: 男
46 likes: 女,学习,人工智能
47 1 row in set (0.00 sec)
48 
49 ERROR: 
50 No query specified
51 
52 mysql> insert into t5 values (2,'情意王子','AID1806','','女,学习,人工智能');
53 Query OK, 1 row affected (0.01 sec)
54 
55 mysql> select * from t5\G;
56 *************************** 1. row ***************************
57    id: 001
58  name: 情意父王
59 class: AID1806
60   sex: 男
61 likes: 女,学习,人工智能
62 *************************** 2. row ***************************
63    id: 002
64  name: 情意王子
65 class: AID1806
66   sex: 男
67 likes: 女,学习,人工智能
68 2 rows in set (0.01 sec)
69 
70 ERROR: 
71 No query specified
72 
73 mysql> 
View Code

  4、日期时间类型
    1、year :年 YYYY
    2、date :日期 YYYYMMDD
    3、time :时间 HHMMSS
    4、datetime :日期时间 YYYYMMDDHHMMSS
    5、timestamp :日期时间 YYYYMMDDHHMMSS
    6、注意
      1、datetime不给值默认返回NULL
      2、timestamp不给值默认返回系统当前时间;

 

 1 mysql> create table t6(
 2     -> id int(3) zerofill,
 3     -> name varchar(15),
 4     -> age tinyint unsigned,
 5     -> birth_year year,
 6     -> birthday date,
 7     -> class time,
 8     -> meeting datetime
 9     -> )default charset=utf8;
10 Query OK, 0 rows affected (0.09 sec)
11 
12 mysql> show tables;
13 +---------------+
14 | Tables_in_db1 |
15 +---------------+
16 | stuinfo       |
17 | stuinfo1806   |
18 | t1            |
19 | t2            |
20 | t3            |
21 | t4            |
22 | t5            |
23 | t6            |
24 +---------------+
25 8 rows in set (0.00 sec)
26 
27 mysql> insert into t6 values
28     -> (1,'百媚英忘',88,1928,19280520,090000,20180601080000);
29 Query OK, 1 row affected (0.06 sec)
30 
31 mysql> select * from t6;
32 +------+--------------+------+------------+------------+----------+---------------------+
33 | id   | name         | age  | birth_year | birthday   | class    | meeting             |
34 +------+--------------+------+------------+------------+----------+---------------------+
35 |  001 | 百媚英忘     |   88 |       1928 | 1928-05-20 | 09:00:00 | 2018-06-01 08:00:00 |
36 +------+--------------+------+------------+------------+----------+---------------------+
37 1 row in set (0.00 sec)
38 
39 mysql> 
40 mysql> create table t7(
41     -> id int(3) zerofill,
42     -> name varchar(15),
43     -> meeting datetime,
44     -> class timestamp
45     -> )default charset=utf8;
46 Query OK, 0 rows affected (0.12 sec)
47 
48 mysql> insert into t7(id,name) values(1,'zhaomin');
49 Query OK, 1 row affected (0.15 sec)
50 
51 mysql> select * from t7;
52 +------+---------+---------+---------------------+
53 | id   | name    | meeting | class               |
54 +------+---------+---------+---------------------+
55 |  001 | zhaomin | NULL    | 2018-06-04 22:49:06 |
56 +------+---------+---------+---------------------+
57 1 row in set (0.01 sec)
58 
59 mysql> desc t7;
60 +---------+--------------------------+------+-----+-------------------+-----------------------------+
61 | Field   | Type                     | Null | Key | Default           | Extra                       |
62 +---------+--------------------------+------+-----+-------------------+-----------------------------+
63 | id      | int(3) unsigned zerofill | YES  |     | NULL              |                             |
64 | name    | varchar(15)              | YES  |     | NULL              |                             |
65 | meeting | datetime                 | YES  |     | NULL              |                             |
66 | class   | timestamp                | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
67 +---------+--------------------------+------+-----+-------------------+-----------------------------+
68 4 rows in set (0.00 sec)
69 
70 mysql> 
View Code

 

8、表字段的操作
  1、语法 :alter  table  表名  执行动作;
    1、添加字段(add)
      1、添加到末尾
        alter  table  表名  add  字段名  数据类型;
      2、添加到开始
        alter table 表名 add 字段名 数据类型 first;
      3、添加到指定位置
        alter table 表名 add 字段名 数据类型 after 字段名
    2、删除字段(drop)
      alter table 表名 drop 字段名;
    3、修改数据类型(modify)
      alter table 表名 modify 字段名 新的数据类型;
    4、修改字段名(change)
      alter table 表名 change 旧名 新名 数据类型;
    5、修改表名(rename)
      alter table 表名 rename 新表名;
作业
1、填空题
1、MySQL中的数据类型有 ____、____、____、____
2、关系型数据库的核心内容是 ___ 即 ___
2、简答题
1、简述客户端把数据存储到数据库服务器上的过程
2、char和varchar的区别?各自的特点
3、操作题
1、创建一个库school
2、在库中创建表students来存储学生信息,字段如下
学号(id) 要求显示宽度为3位,位数不够用0填充
姓名(name)、年龄(age只能为正数)、成绩(score浮点)
性别(sex单选)、爱好(likes多选)、入学时间(年月日)
3、查看students的表结构
4、在students表中增加一个字段id,加在第一列
5、在表中任意插入5条记录
6、查看所有学生的姓名、成绩和入学时间

  1 mysql> create table t8(
  2     -> name varchar(15)
  3     -> )default charset=utf8;
  4 Query OK, 0 rows affected (0.27 sec)
  5 
  6 mysql> alter table t8 add age tinyint unsigned;
  7 Query OK, 0 rows affected (0.86 sec)
  8 Records: 0  Duplicates: 0  Warnings: 0
  9 
 10 mysql> desc t8;
 11 +-------+---------------------+------+-----+---------+-------+
 12 | Field | Type                | Null | Key | Default | Extra |
 13 +-------+---------------------+------+-----+---------+-------+
 14 | name  | varchar(15)         | YES  |     | NULL    |       |
 15 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 16 +-------+---------------------+------+-----+---------+-------+
 17 2 rows in set (0.01 sec)
 18 
 19 mysql> alter table t8 add id int first;
 20 Query OK, 0 rows affected (17.87 sec)
 21 Records: 0  Duplicates: 0  Warnings: 0
 22 
 23 mysql> desc t8;
 24 +-------+---------------------+------+-----+---------+-------+
 25 | Field | Type                | Null | Key | Default | Extra |
 26 +-------+---------------------+------+-----+---------+-------+
 27 | id    | int(11)             | YES  |     | NULL    |       |
 28 | name  | varchar(15)         | YES  |     | NULL    |       |
 29 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 30 +-------+---------------------+------+-----+---------+-------+
 31 3 rows in set (0.19 sec)
 32 
 33 mysql> alter table t8 add sex enum('M','F') after name;
 34 Query OK, 0 rows affected (6.47 sec)
 35 Records: 0  Duplicates: 0  Warnings: 0
 36 
 37 mysql> desc t8;
 38 +-------+---------------------+------+-----+---------+-------+
 39 | Field | Type                | Null | Key | Default | Extra |
 40 +-------+---------------------+------+-----+---------+-------+
 41 | id    | int(11)             | YES  |     | NULL    |       |
 42 | name  | varchar(15)         | YES  |     | NULL    |       |
 43 | sex   | enum('M','F')       | YES  |     | NULL    |       |
 44 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 45 +-------+---------------------+------+-----+---------+-------+
 46 4 rows in set (0.00 sec)
 47 
 48 mysql> 
 49 mysql> alter table t8 drop sex;
 50 Query OK, 0 rows affected (0.83 sec)
 51 Records: 0  Duplicates: 0  Warnings: 0
 52 
 53 mysql> desc t8;
 54 +-------+---------------------+------+-----+---------+-------+
 55 | Field | Type                | Null | Key | Default | Extra |
 56 +-------+---------------------+------+-----+---------+-------+
 57 | id    | int(11)             | YES  |     | NULL    |       |
 58 | name  | varchar(15)         | YES  |     | NULL    |       |
 59 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 60 +-------+---------------------+------+-----+---------+-------+
 61 3 rows in set (0.00 sec)
 62 
 63 mysql> 
 64 mysql> desc t8;
 65 +-------+---------------------+------+-----+---------+-------+
 66 | Field | Type                | Null | Key | Default | Extra |
 67 +-------+---------------------+------+-----+---------+-------+
 68 | id    | int(11)             | YES  |     | NULL    |       |
 69 | name  | varchar(15)         | YES  |     | NULL    |       |
 70 | sex   | enum('M','F')       | YES  |     | NULL    |       |
 71 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 72 +-------+---------------------+------+-----+---------+-------+
 73 4 rows in set (0.00 sec)
 74 
 75 mysql> alter table t8 drop sex;
 76 Query OK, 0 rows affected (0.83 sec)
 77 Records: 0  Duplicates: 0  Warnings: 0
 78 
 79 mysql> desc t8;
 80 +-------+---------------------+------+-----+---------+-------+
 81 | Field | Type                | Null | Key | Default | Extra |
 82 +-------+---------------------+------+-----+---------+-------+
 83 | id    | int(11)             | YES  |     | NULL    |       |
 84 | name  | varchar(15)         | YES  |     | NULL    |       |
 85 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
 86 +-------+---------------------+------+-----+---------+-------+
 87 3 rows in set (0.00 sec)
 88 
 89 mysql> alter t8 modify name char(10);
 90 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 't8 modify name char(10)' at line 1
 91 mysql> alter table t8 modify name char(10);
 92 Query OK, 0 rows affected (1.68 sec)
 93 Records: 0  Duplicates: 0  Warnings: 0
 94 
 95 mysql> desc t8;
 96 +-------+---------------------+------+-----+---------+-------+
 97 | Field | Type                | Null | Key | Default | Extra |
 98 +-------+---------------------+------+-----+---------+-------+
 99 | id    | int(11)             | YES  |     | NULL    |       |
100 | name  | char(10)            | YES  |     | NULL    |       |
101 | age   | tinyint(3) unsigned | YES  |     | NULL    |       |
102 +-------+---------------------+------+-----+---------+-------+
103 3 rows in set (0.07 sec)
104 
105 mysql> alter table t8 change name new_name char(10);
106 Query OK, 0 rows affected (0.16 sec)
107 Records: 0  Duplicates: 0  Warnings: 0
108 
109 mysql> desc t8;
110 +----------+---------------------+------+-----+---------+-------+
111 | Field    | Type                | Null | Key | Default | Extra |
112 +----------+---------------------+------+-----+---------+-------+
113 | id       | int(11)             | YES  |     | NULL    |       |
114 | new_name | char(10)            | YES  |     | NULL    |       |
115 | age      | tinyint(3) unsigned | YES  |     | NULL    |       |
116 +----------+---------------------+------+-----+---------+-------+
117 3 rows in set (0.00 sec)
118 
119 mysql> alter table t8 rename t88;
120 Query OK, 0 rows affected (0.71 sec)
121 
122 mysql> show tables;
123 +---------------+
124 | Tables_in_db1 |
125 +---------------+
126 | stuinfo       |
127 | stuinfo1806   |
128 | t1            |
129 | t2            |
130 | t3            |
131 | t4            |
132 | t5            |
133 | t6            |
134 | t7            |
135 | t88           |
136 +---------------+
137 10 rows in set (0.00 sec)
138 
139 mysql> desc t8;
140 ERROR 1146 (42S02): Table 'db1.t8' doesn't exist
141 mysql> desc t88;
142 +----------+---------------------+------+-----+---------+-------+
143 | Field    | Type                | Null | Key | Default | Extra |
144 +----------+---------------------+------+-----+---------+-------+
145 | id       | int(11)             | YES  |     | NULL    |       |
146 | new_name | char(10)            | YES  |     | NULL    |       |
147 | age      | tinyint(3) unsigned | YES  |     | NULL    |       |
148 +----------+---------------------+------+-----+---------+-------+
149 3 rows in set (0.02 sec)
150 
151 mysql> 
View Code

 

创建数据库utf8

MariaDB [(none)]> show create database django2;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| django2  | CREATE DATABASE `django2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create database `devops` /*!40100 default character set utf8 */ ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| devops             |
| django2            |
| hk_storage         |
| mysql              |
| performance_schema |
| zabbix             |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> show create database devops;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| devops   | CREATE DATABASE `devops` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

 mysql 删除指定一条记录

语法:delete  from  表名  where  条件;

>>>delete  from  table_name  where  id = 1;

 

posted on 2018-06-03 11:30  微子天明  阅读(624)  评论(0编辑  收藏  举报

导航