mysql建库语句on报错_mysql基础学习

最新推荐文章于 2021-03-24 01:19:30 发布

友枝枝

最新推荐文章于 2021-03-24 01:19:30 发布

阅读量229

点赞数

文章标签： mysql建库语句on报错

本文链接：https://blog.csdn.net/weixin_42509815/article/details/113950605

版权

这篇博客详细介绍了在Ubuntu 16.10上安装MySQL的过程，并讲解了DDL语句，包括CREATE DATABASE、DROP DATABASE和ALTER DATABASE的用法，帮助初学者理解MySQL数据库的创建、删除和修改。

摘要由CSDN通过智能技术生成

安装mysql服务器

查看系统版本

开始安装mysql

查看是否安装成功

启动mysql

mysql学习

DDL 定义语言 (CREATE/DROP/ALTER)

DML 操作语言(INSERT/UPDATE/DELETE)

DQL 查询语言(SELECT)

DCL控制语言(GRANT/REVOKE/COMMIT/ROLLBACK)

1.安装mysql服务器

1.1 查看系统版本

yan@yan:~$ cat /etc/issue

Ubuntu16.10\n \l

yan@yan:~$ cat /etc/issue.net

Ubuntu16.10yan@yan:~$ uname -a

Linux yan4.8.0-22-generic #24-Ubuntu SMP Sat Oct 8 09:14:42 UTC 2016 i686 i686 i686 GNU/Linux

yan@yan:~$

1.2 开始安装mysql

root@yan:~# apt-get install mysql-server

root@yan:~# apt-get install mysql-client

root@yan:~# apt-get install libmysqlclient-dev

注意：安装software时，一定要是root用户登录哟，否则会报错(没有权限)

1.3 查看是否安装成功

root@yan:~# [ `ps aux | grep mysql | grep -v "grep" | wc -l` -ge 0 ] && echo -e "Install OK" || echo -e "Install fail"

如果提示Install OK 则表示有进程如果提示Install fail 则可能需要自己重新启动一下程序了

重启命令：root@yan:~# /etc/init.d/mysql restart

1.4 启动mysql

root@yan:~# mysql -uroot -p

其中:mysql 代表mysql程序 -u代表USER -p 代表password

综合起来就是：使用root用户(mysql用户和系统用户是两码事)来登录mysql并且有密码

2.mysql学习

2.1 DDL 定义语言 (主要讲三个:CREATE/DROP/ALTER)

新建数据库(新建了一个数据库，其名称为yan)

mysql> CREATE DATABASEyan;

Query OK,1 row affected (0.00 sec)

查看数据库(可见，yan已经创建成功了)

mysql>SHOW DATABASES;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| yan |

+--------------------+

5 rows in set (0.00 sec)

查看建库语句

mysql> SHOW CREATE DATABASEyan;+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| yan | CREATE DATABASE `yan` /*!40100 DEFAULT CHARACTER SET latin1*/ |

+----------+----------------------------------------------------------------+

1 row in set (0.00sec)

mysql>

删除数据库

mysql> DROP DATABASEyan;

Query OK,0 rows affected (0.00 sec)

再次检查数据库(可见，yan这个数据库已经没有了)

新建数据表

需求：

新建一个购物清单表

新建数据库yan

mysql> CREATE DATABASEyan;

Query OK,1 row affected (0.00 sec)

设置为默认数据库

mysql> USEyan;Database changed

新建表shopping_info

mysql> CREATE TABLEshopping_info (-> id INT(11) AUTO_INCREMENT,-> invoice INT(30) NOT NULL UNIQUE,-> name VARCHAR(50) NOT NULL,-> price FLOAT NOT NULL,-> PRIMARY KEY(id)->);

Query OK,0 rows affected (0.33 sec)

再新建一个收据表

新建表receipt_info

mysql> CREATE TABLEreceipt_info (-> id INT(11) AUTO_INCREMENT,-> date date NOT NULL,-> CONSTRAINT fk FOREIGN KEY(id) REFERENCESshopping_info(id)->);

Query OK,0 rows affected (0.97sec)

mysql>

再新建一个数据表

、

mysql> CREATE TABLEuser_info (-> id VARCHAR(50) UNIQUE NOT NULL,-> name VARCHAR(50) NOT NULL,-> price FLOAT NOT NULL DEFAULT 0,-> age INT(10) NOT NULL DEFAULT 0

->);

Query OK,0 rows affected (0.34sec)

mysql>

ALTER

现在将表user_info更改为user_infomation

mysql> ALTER TABLEuser_info RENAME user_information;

Query OK,0 rows affected (0.14 sec)

修改数据类型，先将price中的FLOAT数据类型修改为DOUBLE类型

mysql> ALTER TABLE user_information MODIFY price DOUBLE;

Query OK,0 rows affected (0.74sec)

Records:0 Duplicates: 0 Warnings: 0mysql>

添加字段English_name至表user_info，位于最前面

mysql> ALTER TABLE user_information ADD english_name VARCHAR(20) NOT NULLFIRST;

Query OK,0 rows affected (0.49sec)

Records:0 Duplicates: 0 Warnings: 0

添加字段English_name1至表user_info，位于name后面

mysql> ALTER TABLE user_information ADD english_name_1 VARCHAR(20) NOT NULLAFTER name;

Query OK,0 rows affected (0.62sec)

Records:0 Duplicates: 0 Warnings: 0

删除字段现在将english_name_1这个字段给删除掉

mysql> ALTER TABLE user_information DROPenglish_name_1;

Query OK,0 rows affected (0.48sec)

Records:0 Duplicates: 0 Warnings: 0

修改字段排序,现在将age这个字段给提到第一

mysql> ALTER TABLE user_information MODIFY age INT(10) NOT NULL DEFAULT 0FIRST;

Query OK,0 rows affected (0.44sec)

Records:0 Duplicates: 0 Warnings: 0

删除receipt_info的外键约束

mysql> ALTER TABLE receipt_info DROP FOREIGN KEYfk;

Query OK,0 rows affected (0.08sec)

Records:0 Duplicates: 0 Warnings: 0

删除数据表shopping_info

mysql> DROP TABLEshopping_info;

Query OK,0 rows affected (0.21 sec)

2.2 DML 操作语言(INSERT/UPDATE/DELETE)

INSERT:

1.向user_information这个表插入数据

mysql> INSERT INTO user_information VALUES (21,‘Ming‘,112,‘ming‘,12.5);

Query OK,1 row affected (0.04 sec)

2.插入部门内容

mysql> INSERT INTO user_information (id,age,english_name,name) VALUES (113,22,‘kun‘,‘Skun‘);

Query OK,1 row affected (0.04 sec)

3.利用单引号来转意

mysql> INSERT INTO user_information VALUES (13,‘xuzhneg‘‘s‘,123345,‘zheng‘‘s‘,123.5);

Query OK,1 row affected (0.05 sec)

UPDATE:

1.修改user_information中age=21的user_information的name值，修改为Li

mysql> UPDATEuser_information-> SET name=‘li‘

-> WHERE age=21;

Query OK,1 row affected (0.09sec)

Rows matched:1 Changed: 1 Warnings: 0

2.多表更新操作

现向表receipt_info插入信息

mysql> INSERT INTO receipt_info VALUES (112,‘2017-3-27‘);

Query OK,1 row affected (0.04 sec)

多表更新，设置receipt.id的值，其中条件为receipt_info.id等于user_information.id的值

mysql> UPDATEreceipt_info,user_information-> SET user_information.age=100

-> WHERE receipt_info.id=user_information.id;

Query OK,1 row affected (0.05sec)

Rows matched:1 Changed: 1 Warnings: 0

DELETE:

1.删除表receipt中id为112的数据

mysql> DELETE FROM receipt_info WHERE id=112;

Query OK,1 row affected (0.04 sec)

2.多表删除数据

现向表receipt_info插入信息

mysql> INSERT INTO receipt_info VALUES (112,‘2017-3-27‘);

Query OK,1 row affected (0.04 sec)

删除user_information和receipt_info表中id相等的字段

mysql> DELETE receipt_info,user_information FROM user_informatiipt_info on,receWHERE user_information.ipt_info.id;

Query OK,2 rows affected (0.04 sec)

2.3 DQL 查询语言(SELECT)

1.简单查询、查询user_information表中所有数据

mysql> SELECT * FROMuser_information;+-----+--------------+--------+---------+-------+

+-----+--------------+--------+---------+-------+

| 22 | kun | 113 | Skun | NULL |

| 13 | xuzhneg‘s | 123345 | zheng‘s | 123.5 |

+-----+--------------+--------+---------+-------+

2 rows in set (0.00 sec)

2.根据条件查询、查询user_information表中age等于22的数据

mysql> SELECT * FROM user_information WHERE age=22;+-----+--------------+-----+------+-------+

+-----+--------------+-----+------+-------+

| 22 | kun | 113 | Skun | NULL |

+-----+--------------+-----+------+-------+

1 row in set (0.00 sec)

3.条件查询user_information表中price为NULL值的数据

mysql> SELECT * FROM user_information WHERE price is NULL;+-----+--------------+-----+------+-------+

+-----+--------------+-----+------+-------+

| 22 | kun | 113 | Skun | NULL |

+-----+--------------+-----+------+-------+

1 row in set (0.00 sec)

4.查询user_information中的数据，并且对age进行排序

mysql> SELECT * FROM user_information ORDER BYage;+-----+--------------+--------+---------+-------+

+-----+--------------+--------+---------+-------+

| 13 | xuzhneg‘s | 123345 | zheng‘s | 123.5 |

| 22 | kun | 113 | Skun | NULL |

+-----+--------------+--------+---------+-------+

2 rows in set (0.00 sec)

5.查询user_information中的数据，并且对age进行排序，且只输入1行

mysql> SELECT * FROM user_information ORDER BY age LIMIT 1;+-----+--------------+--------+---------+-------+

+-----+--------------+--------+---------+-------+

| 13 | xuzhneg‘s | 123345 | zheng‘s | 123.5 |

+-----+--------------+--------+---------+-------+

1 row in set (0.00 sec)

6.模糊匹配、查询user_information中的数据且Name是以z开头的字符串

mysql> SELECT * FROM user_information WHERE name like ‘z%‘;+-----+--------------+--------+---------+-------+

+-----+--------------+--------+---------+-------+

| 13 | xuzhneg‘s | 123345 | zheng‘s | 123.5 |

+-----+--------------+--------+---------+-------+

1 row in set (0.00 sec)

7.模糊匹配、匹配user_information中的id含1的信息

mysql> SELECT * FROM user_information WHERE id like ‘%1%‘;+-----+--------------+--------+---------+-------+

+-----+--------------+--------+---------+-------+

| 22 | kun | 113 | Skun | NULL |

| 13 | xuzhneg‘s | 123345 | zheng‘s | 123.5 |

+-----+--------------+--------+---------+-------+

2 rows in set (0.00 sec)

2.4 DCL 控制语言(GRANT/REVOKE/COMMIT/ROLLBACK)

友枝枝

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql建库语句on报错_mysql基础学习

目录安装mysql服务器查看系统版本开始安装mysql查看是否安装成功启动mysqlmysql学习DDL 定义语言 (CREATE/DROP/ALTER)DML 操作语言(INSERT/UPDATE/DELETE)DQL 查询语言(SELECT)DCL控制语言(GRANT/REVOKE/COMMIT/ROLLBACK)1.安装mysql服务器1.1 查看系统版本yan@yan:~$ cat /et...
复制链接

扫一扫