目录
- 安装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 Ubuntu 16.10 \n \l yan@yan:~$ cat /etc/issue.net Ubuntu 16.10 yan@yan:~$ uname -a Linux yan 4.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 DATABASE yan; 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 DATABASE yan; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | yan | CREATE DATABASE `yan` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
删除数据库
mysql> DROP DATABASE yan; Query OK, 0 rows affected (0.00 sec)
再次检查数据库(可见，yan这个数据库已经没有了)
新建数据表
需求：
新建一个购物清单表
新建数据库yan
mysql> CREATE DATABASE yan; Query OK, 1 row affected (0.00 sec)
设置为默认数据库
mysql> USE yan; Database changed
新建表shopping_info
mysql> CREATE TABLE shopping_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 TABLE receipt_info ( -> id INT(11) AUTO_INCREMENT, -> date date NOT NULL, -> CONSTRAINT fk FOREIGN KEY(id) REFERENCES shopping_info(id) -> ); Query OK, 0 rows affected (0.97 sec) mysql>
再新建一个数据表
、
mysql> CREATE TABLE user_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.34 sec) mysql>
ALTER
现在将表user_info更改为user_infomation
mysql> ALTER TABLE user_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.74 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
添加字段English_name至表user_info，位于最前面
mysql> ALTER TABLE user_information ADD english_name VARCHAR(20) NOT NULL FIRST; Query OK, 0 rows affected (0.49 sec) Records: 0 Duplicates: 0 Warnings: 0
添加字段English_name1至表user_info，位于name后面
mysql> ALTER TABLE user_information ADD english_name_1 VARCHAR(20) NOT NULL AFTER name; Query OK, 0 rows affected (0.62 sec) Records: 0 Duplicates: 0 Warnings: 0
删除字段 现在将english_name_1这个字段给删除掉
mysql> ALTER TABLE user_information DROP english_name_1; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0
修改字段排序,现在将age这个字段给提到第一
mysql> ALTER TABLE user_information MODIFY age INT(10) NOT NULL DEFAULT 0 FIRST; Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0
删除receipt_info的外键约束
mysql> ALTER TABLE receipt_info DROP FOREIGN KEY fk; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
删除数据表shopping_info
mysql> DROP TABLE shopping_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> UPDATE user_information -> SET name='li' -> WHERE age=21; Query OK, 1 row affected (0.09 sec) 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> UPDATE receipt_info,user_information -> SET user_information.age=100 -> WHERE receipt_info.id=user_information.id; Query OK, 1 row affected (0.05 sec) 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 * FROM user_information; +-----+--------------+--------+---------+-------+ | age | english_name | id | name | price | +-----+--------------+--------+---------+-------+ | 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; +-----+--------------+-----+------+-------+ | age | english_name | id | name | price | +-----+--------------+-----+------+-------+ | 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; +-----+--------------+-----+------+-------+ | age | english_name | id | name | price | +-----+--------------+-----+------+-------+ | 22 | kun | 113 | Skun | NULL | +-----+--------------+-----+------+-------+ 1 row in set (0.00 sec)
4.查询user_information中的数据，并且对age进行排序
mysql> SELECT * FROM user_information ORDER BY age; +-----+--------------+--------+---------+-------+ | age | english_name | id | name | price | +-----+--------------+--------+---------+-------+ | 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; +-----+--------------+--------+---------+-------+ | age | english_name | id | name | price | +-----+--------------+--------+---------+-------+ | 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%'; +-----+--------------+--------+---------+-------+ | age | english_name | id | name | price | +-----+--------------+--------+---------+-------+ | 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%'; +-----+--------------+--------+---------+-------+ | age | english_name | id | name | price | +-----+--------------+--------+---------+-------+ | 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)