SQL 语法
文章目录
MySQL启动失败——MySQL服务无法启动原因及解决办法：
(59条消息) MySQL启动失败——MySQL服务无法启动原因及解决办法_Be better。的博客-CSDN博客_mysql无法启动
SQL语言定义了这么几种操作数据库的能力：
DDL：Data Definition Language
DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
DML：Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
DQL：Data Query Language
DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。
数据库表
一个数据库通常包含一个或多个表。每个表有一个名字标识（例如:“Websites”）,表包含带有数据的记录（行）。
解析
- use RUNOOB; 命令用于选择数据库。
- set names utf8; 命令用于设置使用的字符集。
- SELECT * FROM Websites; 读取数据表的信息。
注：
SQL 对大小写不敏感
某些数据库系统要求在每条 SQL 语句的末端使用分号
分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句
使用单引号来环绕文本值，数值不用引号
一些最重要的 SQL 命令
- SELECT - 从数据库中提取数据
- UPDATE - 更新数据库中的数据
- DELETE - 从数据库中删除数据
- INSERT INTO - 向数据库中插入新数据
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更（改变）数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引（搜索键）
- DROP INDEX - 删除索引
语法
MySQL管理
在 Windows 系统下，打开命令窗口(cmd)，进入 MySQL 安装目录的 bin 目录。
初次启动按照从一至最后，非初次启动从四开始。
-
以管理员身份运行命令提示符,右键cmd
-
mysqld --initialize-insecure --user=mysql -
mysqld -install -
net start mysql -
mysql -u root -p -
输入密码出现mysql>
-
quit exit -
net stop mysql
问题：
-
启动mysql 发生系统错误 5。 拒绝访问
右键单击cmd.exe ，以管理员身份运行。
启动mysql成功。
-
net start 后跟服务的名字
-
‘net’ 不是内部或外部命令，也不是可运行的程序
环境配置Path中加%SystemRoot%\system32
进入
mysql -u root -p
退出
exit
用户设置
如果你需要添加 MySQL 用户，你只需要在 mysql 数据库中的 user 表添加新用户即可。
- CREATE USER ‘用户名’@‘主机地址’ IDENTIFIED BY ‘密码’;
【需要root权限】创建用户 本地主机：localhost 任意主机：通配符% 密码可以为空，空的话IDENTIFIED BY也可也不写
- GRANT 权限名称 ON 数据库名.表名 TO ‘用户名’@‘主机地址’;
【需要root权限】授予用户权限 权限名称：SELECT（查询）、INSERT（插入）、UPDATA（更新）等，多个权限用逗号隔开。所有权限用ALL。数据库和数据表的通配符是*
- REVOKE 权限名称 ON 数据库名.表名 FROM ‘用户名’@主机地址;
【需要root权限】撤销用户权限
- SET PASSWORD FOR ‘用户名’@‘主机地址’ = PASSWORD(‘新密码’)
【需要root权限】给任意用户修改密码 PASSWORD函数可以将明文的密码改为密文放在数据库里。
注意：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
- SET PASSWORD = PASSWORD(‘新密码’)
给当前用户修改密码。
- DROP USER ‘用户名’@‘主机地址’;
【需要root权限】删除用户
查看MySQL安装目录 SELECT @@basedir
查看MySQL数据存放目录 SELECT @@datadir
MySQL操作
mysql> show databases;#显示数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
mysql> use school;#进入数据库
Database changed
mysql> show tables;#显示表格
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
mysql> select * from student;#查看表中内容
Empty set (0.00 sec)
mysql> describe student;#查看表格结构
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | varchar(225) | YES | | NULL | |
| age | int | YES | | NULL | |
| tel | int | YES | | NULL | |
| address | varchar(225) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
数据库
创建新数据库
CREAT DATABASES dbname;
create database school character set utf8;
character set utf8
指定数据库采用utf8编码
删除数据库
drop database <数据库名>
表
创建新表
CREATE TABLE table_name
(
column_name1 data_type(slze),
column_name2 data_type(slze),
column_name3 data_type(slze),
......
);
column_name1->参数规定表中列的名称
data_type(slze)->参数规定列的数据类型（例如archar,integer,declmal,data等等）
| 数据类型 | 含义 |
|---|---|
| CHAR(n),CHARACTER(n) | 长度为n的定长字符串 |
| VARCHAR(n),CHARACTERVARING(n) | 最大长度为n的变长字符串 |
| INT | 长整数 |
| FLOAT(n) | 可选精度的浮点数，精度至少为n位数字 |
| BOOLEAN | 逻辑布尔量 |
| DATE | 日期，年月日，YYYY-MM-DD |
| TIME | 时间，时分秒，HH-MM-SS |
| TIMESTAMP | 时间戳类型 |
| INTERVAL | 时间间隔类型 |
mysql> create table student(id int,name varchar(255),sex varchar(225),age int,tel int,address varchar(225));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
查询表中数据
mysql> select * from student;#查看表中内容
Empty set (0.00 sec)
mysql> describe student;#查看表格结构
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | varchar(225) | YES | | NULL | |
| age | int | YES | | NULL | |
| tel | int | YES | | NULL | |
| address | varchar(225) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
向表中插入数据
mysql> select * from student;
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
+------+------+------+------+------+------------+
1 row in set (0.00 sec)
mysql> insert into student(id,sex,name,tel,age,address) values (120,'女','mary',120,16,'shanghai');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 120 | mary | 女 | 16 | 120 | shanghai |
+------+------+------+------+------+------------+
2 rows in set (0.00 sec)
修改基本表
alter table <表名> add <新列名> <数据类型>
mysql> alter table student add cname varchar(225);
Query OK, 0 rows affected (0.04 sec)
查询表中数据
SELECT column_name,column_name
FROM table_name
WHEREcolumn_name operator valu
mysql> select * from student;#显示全部内容
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 120 | mary | 女 | 16 | 120 | shanghai |
+------+------+------+------+------+------------+
2 rows in set (0.00 sec)
mysql> select name from student;
+------+
| name |
+------+
| tom |
| mary |
+------+
2 rows in set (0.00 sec)
mysql> select * from student where sex='男';
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
+------+------+------+------+------+------------+
1 row in set (0.00 sec)
where子句中的运算符
'='等于
‘<>‘不等于 注：一些版本中写作’!=’
'>'大于
'<'小于
'>='大于等于
'<='小于等于
BETWEEN 在某个范围内
mysql> select * from student where age between 15 and 20;
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 120 | mary | 女 | 16 | 120 | shanghai |
+------+------+------+------+------+------------+
2 rows in set (0.00 sec)
LIKE 搜索某种模式
mysql> select * from student where address like '%ang%';
+------+------+------+------+------+----------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+----------+
| 120 | mary | 女 | 16 | 120 | shanghai |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)
IN 指定指针对某个列的多个可能值
mysql> select * from student where tel in (110,119);
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
+------+------+------+------+------+------------+
1 row in set (0.00 sec)
ORDER BY关键字
用于对一个结果集按照一个列或者多个列进行排序
关键字默认按照升序对记录进行排序
若需降序，则使用DESC关键字
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
mysql> select * from student order by name;
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 120 | mary | 女 | 16 | 120 | shanghai |
| 1 | tom | 男 | 18 | 110 | washington |
+------+------+------+------+------+------------+
2 rows in set (0.00 sec)
更新表中数据
UPDATE语句
UPDATE <表名> SET column1=value1,column2=value2,…
WHERE some_column=some_value;
mysql> select * from student;
+------+--------+--------+------+------+------------+
| id | name | sex | age | tel | address |
+------+--------+--------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 120 | mary | 女 | 16 | 120 | shanghai |
| 3 | tom | male | 28 | 123 | changsha |
| 4 | alex | female | 58 | 444 | haerbin |
| 5 | sophla | female | 18 | 666 | beijing |
| 6 | allce | female | 8 | 555 | beijing |
| 7 | Jerry | male | 22 | 333 | tianjing |
+------+--------+--------+------+------+------------+
7 rows in set (0.00 sec)
mysql> update student set id=2 where name='mary';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+------+--------+--------+------+------+------------+
| id | name | sex | age | tel | address |
+------+--------+--------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 2 | mary | 女 | 16 | 120 | shanghai |
| 3 | tom | male | 28 | 123 | changsha |
| 4 | alex | female | 58 | 444 | haerbin |
| 5 | sophla | female | 18 | 666 | beijing |
| 6 | allce | female | 8 | 555 | beijing |
| 7 | Jerry | male | 22 | 333 | tianjing |
+------+--------+--------+------+------+------------+
7 rows in set (0.00 sec)
删除表中数据
DELETE语句
DELETE FROM table_name
WHERE some_column=some_value;
若没有WHERE语句，表中所有数据将被删除
mysql>delete from student where name='Jerry';
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+--------+--------+------+------+------------+
| id | name | sex | age | tel | address |
+------+--------+--------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 2 | mary | 女 | 16 | 120 | shanghai |
| 3 | tom | male | 28 | 123 | changsha |
| 4 | alex | female | 58 | 444 | haerbin |
| 5 | sophla | female | 18 | 666 | beijing |
| 6 | allce | female | 8 | 555 | beijing |
+------+--------+--------+------+------+------------+
6 rows in set (0.00 sec)
LIMIT子句
限制从数据表中返回指定记录的数目
SELECT column_name(s)
FROM table_name
LIMIT number;
mysql> select * from student where address like '%ing%' limit 2;
+------+--------+--------+------+------+------------+
| id | name | sex | age | tel | address |
+------+--------+--------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 5 | sophla | female | 18 | 666 | beijing |
+------+--------+--------+------+------+------------+
2 rows in set (0.00 sec)
LIKE操作符
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
mysql> select * from student where name like 'a%' limit 2;#选取首字母为‘a'的
+------+-------+--------+------+------+---------+
| id | name | sex | age | tel | address |
+------+-------+--------+------+------+---------+
| 4 | alex | female | 58 | 444 | haerbin |
| 6 | allce | female | 8 | 555 | beijing |
+------+-------+--------+------+------+---------+
2 rows in set (0.00 sec)
'%'符号用于在模式的前后定义定义通配符（缺省字母）
通配符
与LIKE操作符一起使用
%替代0个或多个字符
_替代一个字符
[charlist]字符列中任何单一字符
[^charlist]或[!charlist]不在字符列中的
mysql> select * from student where name like '_o%';
+------+--------+--------+------+------+------------+
| id | name | sex | age | tel | address |
+------+--------+--------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 3 | tom | male | 28 | 123 | changsha |
| 5 | sophla | female | 18 | 666 | beijing |
+------+--------+--------+------+------+------------+
3 rows in set (0.00 sec)
mysql> select * from student where address regexp '[s,c].*';#正则表达式，地址里包含s，c，其余随意
+------+------+------+------+------+------------+
| id | name | sex | age | tel | address |
+------+------+------+------+------+------------+
| 1 | tom | 男 | 18 | 110 | washington |
| 2 | mary | 女 | 16 | 120 | shanghai |
| 3 | tom | male | 28 | 123 | changsha |
+------+------+------+------+------+------------+
3 rows in set (0.00 sec)
IN操作符
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2......);
BETWEEN操作符
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 and value2;
别名
列的SQL别名语法
SELECT column_name AS allas_name FROM table_name;
表的SQL别名语法
SELECT column_name(s) AS FROM table_name as allas_name;
JOIN连接
用于将两个或多个表连接起来
JOIN类型
INNER JOIN如果表中至少一个匹配，则返回行
LEFT JOIN即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN即使左表中没有匹配，也从右表返回所有的行
FULL JOIN只要其中一个表中存在匹配，则返回行
mysql> select * from student inner join score on sid=id;
+------+--------+--------+------+------+------------+------+---------+------+---------+
| id | name | sex | age | tel | address | sid | chinese | math | english |
+------+--------+--------+------+------+------------+------+---------+------+---------+
| 1 | tom | 男 | 18 | 110 | washington | 1 | 98 | 32 | 78 |
| 2 | mary | 女 | 16 | 120 | shanghai | 2 | 45 | 92 | 68 |
| 3 | tom | male | 28 | 123 | changsha | 3 | 65 | 82 | 35 |
| 4 | alex | female | 58 | 444 | haerbin | 4 | 78 | 23 | 44 |
| 5 | sophla | female | 18 | 666 | beijing | 5 | 56 | 100 | 99 |
| 6 | allce | female | 8 | 555 | beijing | 6 | 96 | 100 | 100 |
+------+--------+--------+------+------+------------+------+---------+------+---------+
6 rows in set (0.00 sec)
左连
mysql> select * from student left join score on sid=2;
+------+--------+--------+------+------+------------+------+---------+------+---------+
| id | name | sex | age | tel | address | sid | chinese | math | english |
+------+--------+--------+------+------+------------+------+---------+------+---------+
| 1 | tom | 男 | 18 | 110 | washington | 2 | 45 | 92 | 68 |
| 2 | mary | 女 | 16 | 120 | shanghai | 2 | 45 | 92 | 68 |
| 3 | tom | male | 28 | 123 | changsha | 2 | 45 | 92 | 68 |
| 4 | alex | female | 58 | 444 | haerbin | 2 | 45 | 92 | 68 |
| 5 | sophla | female | 18 | 666 | beijing | 2 | 45 | 92 | 68 |
| 6 | allce | female | 8 | 555 | beijing | 2 | 45 | 92 | 68 |
+------+--------+--------+------+------+------------+------+---------+------+---------+
6 rows in set (0.00 sec)
右连
mysql> select * from student right join score on id=2;
+------+------+------+------+------+----------+------+---------+------+---------+
| id | name | sex | age | tel | address | sid | chinese | math | english |
+------+------+------+------+------+----------+------+---------+------+---------+
| 2 | mary | 女 | 16 | 120 | shanghai | 1 | 98 | 32 | 78 |
| 2 | mary | 女 | 16 | 120 | shanghai | 2 | 45 | 92 | 68 |
| 2 | mary | 女 | 16 | 120 | shanghai | 3 | 65 | 82 | 35 |
| 2 | mary | 女 | 16 | 120 | shanghai | 4 | 78 | 23 | 44 |
| 2 | mary | 女 | 16 | 120 | shanghai | 5 | 56 | 100 | 99 |
| 2 | mary | 女 | 16 | 120 | shanghai | 6 | 96 | 100 | 100 |
+------+------+------+------+------+----------+------+---------+------+---------+
6 rows in set (0.00 sec)
约束Constraints
用于规定表中的数据规则
约束可以在创建表时规定 (CREAT TABLE)，或者在创建表后(ALTER TABLE)
CREAT TABLE table_name
(
column_name1 data_type(slze) constralnt_name,
column_name2 data_type(slze) constralnt_name,
column_name3 data_type(slze) constralnt_name,
…
);
约束类型
NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键（一个表指向另一个表）
CHECK 检查
DEFAULT 默认
撤销删除
DROP INDEX删除索引
DROP TABLE删除表
DROP DATABASE删除数据库
TRUNCATE TALE删除表内数据
修改ALTER
在表中添加列
ALTER TABLE table_name
ADD column_name datatype
删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name
改变表中数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
自增字段
AUTO-INCREMENT
CREAT TABLE table_name
(
ID int NOT NULL AUTO-INCREMENT,
…
定 (CREAT TABLE)，或者在创建表后(ALTER TABLE)
CREAT TABLE table_name
(
column_name1 data_type(slze) constralnt_name,
column_name2 data_type(slze) constralnt_name,
column_name3 data_type(slze) constralnt_name,
…
);
约束类型
NOT NULL 非空
UNIQUE 唯一
PRIMARY KEY 主键
FOREIGN KEY 外键（一个表指向另一个表）
CHECK 检查
DEFAULT 默认
撤销删除
DROP INDEX删除索引
DROP TABLE删除表
DROP DATABASE删除数据库
TRUNCATE TALE删除表内数据
修改ALTER
在表中添加列
ALTER TABLE table_name
ADD column_name datatype
删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name
改变表中数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
自增字段
AUTO-INCREMENT
CREAT TABLE table_name
(
ID int NOT NULL AUTO-INCREMENT,
…
);
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
