mysql 的 sql语言_mysql-sql语言参考

字段去重查询

select distinct style from music

批量修改某字段

update music set style = "ost"  where style like "%影视剧%"

先查询，再添加或修改数据字段

select * from music where language  is null

update music set language = "外国" where language is null

mysql的sql实现

mysql> alter table 国产 rename to guochan;

习惯上函数用大写，但小写也可以

mysql> select now();

select ascii('f');

返回字符串str的第一个字符的ASCII值(str是空串时返回0),总是加单引号。

select CHAR(77,121,83,86,77);

增删改查语句

MariaDB[EMP]> insert into Employees values (100,18,'aa','bb');

Query OK,1 row affected (0.02sec)

MariaDB[EMP]> insert into Employees values (101,19,'a2','b2');

Query OK,1 row affected (0.02sec)

MariaDB[EMP]> insert into Employees values (101,19,'zara','b3');

Query OK,1 row affected (0.01sec)

MariaDB[EMP]> insert into Employees values (105,30,'a4','b4');

Query OK,1 row affected (0.01sec)

MariaDB[EMP]> select * fromEmployees;+-----+-----+-------+------+

| id | age | first | last |

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

| 100 | 18 | aa | bb |

| 101 | 19 | a2 | b2 |

| 101 | 19 | zara | b3 |

| 105 | 30 | a4 | b4 |

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

4 rows in set (0.00sec)

MariaDB[EMP]> UPDATE Employees SET id=102 WHERE first='zara';

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0MariaDB[EMP]> select * fromEmployees;+-----+-----+-------+------+

| id | age | first | last |

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

| 100 | 18 | aa | bb |

| 101 | 19 | a2 | b2 |

| 102 | 19 | zara | b3 |

| 105 | 30 | a4 | b4 |

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

4 rows in set (0.00sec)

MariaDB[EMP]> delete from Employees where id=105;

MariaDB[EMP]> select * fromEmployees;+-----+-----+-------+------+

| id | age | first | last |

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

| 100 | 18 | aa | bb |

| 101 | 19 | a2 | b2 |

| 102 | 19 | zara | b3 |

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

3 rows in set (0.00sec)

MariaDB[EMP]> insert into Employees values(108,29,'wu','ma');

Query OK,1 row affected (0.03sec)

MariaDB[EMP]> insert into Employees values(109,26,'wang','wei');

Query OK,1 row affected (0.02sec)

MariaDB[EMP]> select * fromEmployees;+-----+-----+-------+------+

| id | age | first | last |

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

| 100 | 18 | aa | bb |

| 101 | 19 | a2 | b2 |

| 102 | 19 | zara | b3 |

| 108 | 29 | wu | ma |

| 109 | 26 | wang | wei |

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

5 rows in set (0.00 sec)

show variables like '%FOREIGN%';

SET FOREIGN_KEY_CHECKS=0;

delete from geographical_position where type = 1

select distinct type from geographical_position

mysql> create user 'ab'@'localhost' identified by 'weeee';

mysql> select user,host,password from mysql.user;

INSERT(str,pos,len,newstr)

instr

mysql> select instr('contents','t');

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

| instr('contents','t') |

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

|                     4 |

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

1 row in set (0.00 sec)

mysqldump -u root -d -R --add-drop-table spa >spa.sql

sed -i '1i\use spa;' spa.sql

sed -i '1i\create database spa;' spa.sql

sed -i '1i\drop database if exists spa;' spa.sql

mysqldump -u root   --add-drop-table spa action_buttons>>spa.sql

mysqldump -u root   --add-drop-table spa menu>>spa.sql

mysqldump -u root   --add-drop-table --extended-insert=false spa tb_sequence>>spa.sql

mysqldump -u root   --add-drop-table spa role --where="id=20" >>spa.sql

mysqldump -u root   --add-drop-table spa role_button_relation --where="role_id=20" >>spa.sql

mysqldump -u root   --add-drop-table spa user_role_relation --where="role_id=20" >>spa.sql

mysqldump -u root   --add-drop-table spa system_user --where="username='admin'" >>spa.sql

-d, --no-data       No row information.

-R, --routines      Dump stored routines (functions and procedures).

select * from tb_sequence limit 0,1

limit是mysql的语法

select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录

n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

即取出第3条至第6条，4条记录

[root@db mysql]# mysqlshow

[root@db mysql]# mysql -e 'select user,password,host from mysql.user'

mysql> delete from user where user = '';    删除匿名用户

mysql> drop user ''@localhost ;