mysql-sql语言参考
2023-09-27 14:23:14 时间
字段去重查询
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.02 sec) MariaDB [EMP]> insert into Employees values (101,19,'a2','b2'); Query OK, 1 row affected (0.02 sec) MariaDB [EMP]> insert into Employees values (101,19,'zara','b3'); Query OK, 1 row affected (0.01 sec) MariaDB [EMP]> insert into Employees values (105,30,'a4','b4'); Query OK, 1 row affected (0.01 sec) MariaDB [EMP]> select * from Employees; +-----+-----+-------+------+ | 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.00 sec) MariaDB [EMP]> UPDATE Employees SET id=102 WHERE first='zara'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [EMP]> select * from Employees; +-----+-----+-------+------+ | 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.00 sec) MariaDB [EMP]> delete from Employees where id=105; MariaDB [EMP]> select * from Employees; +-----+-----+-------+------+ | id | age | first | last | +-----+-----+-------+------+ | 100 | 18 | aa | bb | | 101 | 19 | a2 | b2 | | 102 | 19 | zara | b3 | +-----+-----+-------+------+ 3 rows in set (0.00 sec) MariaDB [EMP]> insert into Employees values(108,29,'wu','ma'); Query OK, 1 row affected (0.03 sec) MariaDB [EMP]> insert into Employees values(109,26,'wang','wei'); Query OK, 1 row affected (0.02 sec) MariaDB [EMP]> select * from Employees; +-----+-----+-------+------+ | 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 ;
相关文章
- Spark如何写入HBase/Redis/MySQL/Kafka
- MySQL 8.0从入门到精通
- MySQL SQL优化
- go语言连接mysql、sqlx、sql注入
- mysql排名
- python3.6 连接mysql数据库
- 基于Jsp+Servlet+Mysql实现的javaweb机房课表管理系统
- MYSQL数据库-索引
- MySQL SQL_Mode
- mysql中char,varchar与text类型的区别和选用
- MySQL 操作命令梳理(5)-执行sql语句查询即mysql状态说明
- python使用插入带有%的字符串到mysql数据库
- [lua, mysql] 将多条记录数据组合成一条sql插入语句(for mysql)
- PostgreSQL与MySQL比较
- sql查询报java.sql.SQLException: Column 'LC_ID' not found 的错误实际上是mysql在hibernate别名的问题
- MySQL sql_mode=only_full_group_by错误
- mysql和oracle的区别(功能性能、选择、使用它们时的sql等对比)
- 怎么打开/查看MySQL的SQL记录
- 三种记录 MySQL 所执行过的 SQL 语句的方法
- 如何在MySQL中导入大容量SQL文件
- 2023-03-08 MySQL源码分析-数据如何从SQL层传递到innodb引擎层
- 大意了,一次MySQL分页导致的线上事故...
- mysql:php操作mysql基础总结
- mysql 数据库查询最后两条数据
- 将SQL文件导入MySql
- mysql 导入sql脚本中文乱码问题
- SQL Server 用链接服务器 同步SqlServer与MySQL
- MySQL 查询表结构
- mysql metadata lock锁
- 为什么python程序中查询MySQL的语句在数据表数据增加之后,查询到的结果却没变,还是原来的数据量?
- python - pandas读取excel示例 - 链接mysql示例
- mysql分表