Mac安装mysql数据库&MySQL基础和MySQL在开发中常用的技术

最新推荐文章于 2024-03-24 22:01:01 发布

Walden_tinghou

最新推荐文章于 2024-03-24 22:01:01 发布

阅读量1.1k

点赞数

分类专栏：数据库 Java

本文链接：https://blog.csdn.net/sunnyboy9/article/details/53369669

版权

Java 同时被 2 个专栏收录

39 篇文章 1 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

一.安装步骤

打开终端，输入：

sudo vi ~/.bash_profile

输入 i

然后粘贴以下内容

# mysql

alias mysql='/usr/local/mysql/bin/mysql'

alias mysqladmin='/usr/local/mysql/bin/mysqladmin'

# ls

alias ls='ls -G'

按ESC键

输入 :wq

重新启动终端程序

在命令行中输入下面命令修改密码

mysqladmin -u root password "123456"

Last login: Sun Nov 27 22:27:51 on ttys000

tinghoudeMacBook:~ tinghou$ ls

Desktop Downloads Movies Pictures VM Library cd

Documents Library Music Public Workspaces

tinghoudeMacBook:~ tinghou$ cd /usr/local/mysql/bin/

tinghoudeMacBook:bin tinghou$ ls

innochecksum mysqlaccess

msql2mysql mysqlaccess.conf

my_print_defaults mysqladmin

myisam_ftdump mysqlbinlog

myisamchk mysqlbug

myisamlog mysqlcheck

myisampack mysqld

mysql mysqld-debug

mysql_client_test mysqld_multi

mysql_client_test_embedded mysqld_safe

mysql_config mysqldump

mysql_config_editor mysqldumpslow

mysql_convert_table_format mysqlhotcopy

mysql_embedded mysqlimport

mysql_find_rows mysqlshow

mysql_fix_extensions mysqlslap

mysql_plugin mysqltest

mysql_secure_installation mysqltest_embedded

mysql_setpermission perror

mysql_tzinfo_to_sql replace

mysql_upgrade resolve_stack_dump

mysql_waitpid resolveip

mysql_zap

tinghoudeMacBook:bin tinghou$ cd

tinghoudeMacBook:~ tinghou$ mysql -u root -p

Enter password: tinghoudeMacBook

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 171

Server version: 5.6.12 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.01 sec)

mysql> use test;

Database changed

mysql> exit;

Bye

tinghoudeMacBook:~ tinghou$

bogon:~ tinghou$ mysqladmin -u root password "123456"

bogon:~ tinghou$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 61

Server version: 5.6.12 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> create database textdb1;

Query OK, 1 row affected (0.00 sec)

mysql> show create database textdb1;

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

| Database | Create Database |

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

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

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

1 row in set (0.00 sec)

mysql> use textdb1;

Database changed

mysql> select database();

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

| database() |

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

| textdb1 |

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

1 row in set (0.00 sec)

mysql> select database();

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

| database() |

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

| textdb1 |

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

1 row in set (0.00 sec)

mysql> create table t_user(

-> id int,

-> name varchar(20),

-> sal double(4,3),

-> birthday datetime,

-> hiredate timestamp

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

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

| Tables_in_textdb1 |

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

| t_user |

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

1 row in set (0.00 sec)

mysql> desc t_user;

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

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

| id | int(11) | YES | | NULL | |

| sal | double(4,3) | YES | | NULL | |

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

5 rows in set (0.01 sec)

mysql>

2.测试：

首先创建一个测试用的数据库

mysql> create database day06;

Query OK, 1 row affected (0.00 sec)

mysql> use day06;

Database changed

mysql> create table users(

-> id int primary key auto_increment,

-> name varchar(40),

-> password varchar(40),

-> email varchar(60),

-> birthday date,

-> )character set utf8 collate utf8_general_ci;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')character set utf8 collate utf8_general_ci' at line 7

mysql> create table users(

-> id int primary key auto_increment,

-> name varchar(40),

-> password varchar(40),

-> email varchar(60),

-> birthday date

-> )character set utf8 collate utf8_general_ci;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into users(name,password,email,birthday) values('zs','123456','zs@sina.com','1980-12-04');

Query OK, 1 row affected (0.00 sec)

mysql> insert into users(name,password,email,birthday) values('ls','123','ls@sina.com','1990-01-01');

Query OK, 1 row affected (0.01 sec)

mysql>

接着编写Java代码：

package com.me.jdbcdemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//使用JDBC技术实现查询数据库数据，并显示在控制台中
public class Demo1 {

	public static void main(String[] args) throws Exception {
		
	
		
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			
			
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day06", "root", "123456");
			
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery("select password,email,birthday,id,name from users");
			
			 
			while(rs.next()){ 
				System.out.println(rs.getObject("password"));
				System.out.println(rs.getObject("id"));
				System.out.println(rs.getObject("name"));
				System.out.println(rs.getObject("birthday"));
				System.out.println(rs.getObject("email"));
				System.out.println("-----------------");
			}
		
			
			rs.close();
			stmt.close();
			conn.close();
			
		
	}

}

运行：enjoy it！

二.常见的SQL语句

bogon:~ tinghou$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 98

Server version: 5.6.12 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| day06 |

| mysql |

| performance_schema |

| test |

| testdb |

| textdb1 |

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

7 rows in set (0.01 sec)

mysql> use day06;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> net stop mysql;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'net stop mysql' at line 1

mysql> show tables;

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

| Tables_in_day06 |

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

| users |

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

1 row in set (0.00 sec)

mysql> desc users;

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

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

5 rows in set (0.02 sec)

mysql> select *from users;

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

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

| 1 | zs | 123456 | zs@sina.com | 1980-12-04 |

| 2 | ls | 123 | ls@sina.com | 1990-01-01 |

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

2 rows in set (0.00 sec)

mysql> select name from users;

+------+

| name |

+------+

| zs |

| ls |

+------+

2 rows in set (0.00 sec)

mysql> /*从新创建一个数据库*/

mysql> /*创建一个雇员表体验常见的数据库操作*/

mysql> create table emp(

-> empno int,

-> ename varchar(50),

-> job varchar(50),

-> mgr int,

-> hiredate date,

-> sal decimal(7,2),

-> comm decimal(7,2),

-> deptno int

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> show emp;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp' at line 1

mysql> desc emp;

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

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

| empno | int(11) | YES | | NULL | |

| job | varchar(50) | YES | | NULL | |

| mgr | int(11) | YES | | NULL | |

| sal | decimal(7,2) | YES | | NULL | |

| deptno | int(11) | YES | | NULL | |

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

8 rows in set (0.01 sec)

mysql> /*查询所有列*/

mysql> select * from emp;

Empty set (0.00 sec)

mysql> INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;

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

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

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

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

14 rows in set (0.00 sec)

mysql> /*查询指定列*/

mysql> select ename from emp;

+--------+

| ename |

+--------+

| SMITH |

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |

| ADAMS |

| JAMES |

| FORD |

| MILLER |

+--------+

14 rows in set (0.00 sec)

mysql> select ename,job from emp;

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

| ename | job |

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

| SMITH | CLERK |

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| JONES | MANAGER |

| MARTIN | SALESMAN |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| SCOTT | ANALYST |

| KING | PRESIDENT |

| TURNER | SALESMAN |

| ADAMS | CLERK |

| JAMES | CLERK |

| FORD | ANALYST |

| MILLER | CLERK |

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

14 rows in set (0.00 sec)

mysql> /*创建一个学生表*/

mysql> create table stu(

-> sid char(6),

-> sname varchar(50),

-> age int,

-> gender varchar(50)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

Query OK, 1 row affected (0.00 sec)

mysql> desc stu;

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

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

| sid | char(6) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

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

4 rows in set (0.01 sec)

mysql> select *from stu;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1002 | chenEr | 15 | female |

| S_1003 | zhangSan | 95 | male |

| S_1004 | liSi | 65 | female |

| S_1005 | wangWu | 55 | male |

| S_1006 | zhaoLiu | 75 | female |

| S_1007 | sunQi | 25 | male |

| S_1008 | zhouBa | 45 | female |

| S_1009 | wuJiu | 85 | male |

| S_1010 | zhengShi | 5 | female |

| S_1011 | xxx | NULL | NULL |

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

11 rows in set (0.00 sec)

mysql> /*查询性别为女，并且年龄为50的记录*/

mysql> select * from stu where gender='female' and age<50;

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

| sid | sname | age | gender |

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

| S_1002 | chenEr | 15 | female |

| S_1008 | zhouBa | 45 | female |

| S_1010 | zhengShi | 5 | female |

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

3 rows in set (0.00 sec)

mysql> /*查询学号为s_1001,s_1002)的记录*/

mysql> select * from stu where sid in('s_1001','s_1002');

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1002 | chenEr | 15 | female |

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

2 rows in set (0.00 sec)

mysql> /*查询学号为s_1001，或者姓名为liuYi的记录*/

mysql> select * from stu where sid ='s_1001' or sname = 'liuYi';

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

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

1 row in set (0.00 sec)

mysql> /*查询学号不是S_1001，S_1002，S_1003的记录*/

mysql> SELECT * FROM tab_student

-> WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

ERROR 1146 (42S02): Table 'day06.tab_student' doesn't exist

mysql> SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

ERROR 1146 (42S02): Table 'day06.tab_student' doesn't exist

mysql> SELECT * FROM stu WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

ERROR 1054 (42S22): Unknown column 's_number' in 'where clause'

mysql> select * from stu where sid not in('S_1001','S_1002','S_1003');

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

| sid | sname | age | gender |

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

| S_1004 | liSi | 65 | female |

| S_1005 | wangWu | 55 | male |

| S_1006 | zhaoLiu | 75 | female |

| S_1007 | sunQi | 25 | male |

| S_1008 | zhouBa | 45 | female |

| S_1009 | wuJiu | 85 | male |

| S_1010 | zhengShi | 5 | female |

| S_1011 | xxx | NULL | NULL |

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

8 rows in set (0.00 sec)

mysql> /*查询年龄为Null的记录*/

mysql> select *from stu where age is null;

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

| sid | sname | age | gender |

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

| S_1011 | xxx | NULL | NULL |

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

1 row in set (0.00 sec)

mysql> /*查询年龄在20岁到40岁之间的学生*/

mysql> select *from stu where age>=20 and age<=40;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1007 | sunQi | 25 | male |

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

2 rows in set (0.00 sec)

mysql> select *from stu where age between 20 and 40;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1007 | sunQi | 25 | male |

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

2 rows in set (0.01 sec)

mysql> /*查询性别非男生的学生记录*/

mysql> select *from stu where gender<>'male';

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

| sid | sname | age | gender |

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

| S_1002 | chenEr | 15 | female |

| S_1004 | liSi | 65 | female |

| S_1006 | zhaoLiu | 75 | female |

| S_1008 | zhouBa | 45 | female |

| S_1010 | zhengShi | 5 | female |

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

5 rows in set (0.00 sec)

mysql> select *from stu where not gender='male';

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

| sid | sname | age | gender |

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

| S_1002 | chenEr | 15 | female |

| S_1004 | liSi | 65 | female |

| S_1006 | zhaoLiu | 75 | female |

| S_1008 | zhouBa | 45 | female |

| S_1010 | zhengShi | 5 | female |

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

5 rows in set (0.01 sec)

mysql> /*查询姓名不为null的记录*/

mysql> select *from stu where sname is not null;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1002 | chenEr | 15 | female |

| S_1003 | zhangSan | 95 | male |

| S_1004 | liSi | 65 | female |

| S_1005 | wangWu | 55 | male |

| S_1006 | zhaoLiu | 75 | female |

| S_1007 | sunQi | 25 | male |

| S_1008 | zhouBa | 45 | female |

| S_1009 | wuJiu | 85 | male |

| S_1010 | zhengShi | 5 | female |

| S_1011 | xxx | NULL | NULL |

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

11 rows in set (0.00 sec)

mysql> select *from stu where not sname is null;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1002 | chenEr | 15 | female |

| S_1003 | zhangSan | 95 | male |

| S_1004 | liSi | 65 | female |

| S_1005 | wangWu | 55 | male |

| S_1006 | zhaoLiu | 75 | female |

| S_1007 | sunQi | 25 | male |

| S_1008 | zhouBa | 45 | female |

| S_1009 | wuJiu | 85 | male |

| S_1010 | zhengShi | 5 | female |

| S_1011 | xxx | NULL | NULL |

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

11 rows in set (0.00 sec)

mysql> /*模糊查询：当想查询mysql> 模糊查询：当想查询姓名当中包含了字母a的时候询mysql> 模糊查询：当想查询姓名当中包含了字母a的时候就需要使用模糊查询了。模糊查询需要使用关键字 like 通配符： _ 任意一个字符 %：任意0~n个字符 '%张%' '张_'*/

-> select *from stu;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '模糊查询：当想查询姓名当中包含了字母a的时候就需要使用' at line 1

mysql> /*查询由5个字母构成的学生记录*/

mysql> select * from stu where sname like '-----';

Empty set (0.00 sec)

mysql> select *from stu where sname like '---'

-> ;

Empty set (0.00 sec)

mysql> select *from stu where sname like '--';

Empty set (0.00 sec)

mysql> select * from stu where sname LIKE '------';

Empty set (0.00 sec)

mysql> /*查询姓名由5个字母构成，并且第5个字母为“i”的学生记录*/

mysql> select * from stu where sname like '----i';

Empty set (0.00 sec)

mysql> select from stu;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from stu' at line 1

mysql> selcet *from stu;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selcet *from stu' at line 1

mysql> select * from stu;

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

| sid | sname | age | gender |

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

| S_1001 | liuYi | 35 | male |

| S_1002 | chenEr | 15 | female |

| S_1003 | zhangSan | 95 | male |

| S_1004 | liSi | 65 | female |

| S_1005 | wangWu | 55 | male |

| S_1006 | zhaoLiu | 75 | female |

| S_1007 | sunQi | 25 | male |

| S_1008 | zhouBa | 45 | female |

| S_1009 | wuJiu | 85 | male |

| S_1010 | zhengShi | 5 | female |

| S_1011 | xxx | NULL | NULL |

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

11 rows in set (0.00 sec)

mysql> /**去去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：*/

mysql> select distinct sal from emp;

+---------+

| sal |

+---------+

| 800.00 |

| 1600.00 |

| 1250.00 |

| 2975.00 |

| 2850.00 |

| 2450.00 |

| 3000.00 |

| 5000.00 |

| 1500.00 |

| 1100.00 |

| 950.00 |

| 1300.00 |

+---------+

12 rows in set (0.01 sec)

mysql> select sal from emp;

+---------+

| sal |

+---------+

| 800.00 |

| 1600.00 |

| 1250.00 |

| 2975.00 |

| 1250.00 |

| 2850.00 |

| 2450.00 |

| 3000.00 |

| 5000.00 |

| 1500.00 |

| 1100.00 |

| 950.00 |

| 3000.00 |

| 1300.00 |

+---------+

14 rows in set (0.00 sec)

mysql> /*查看雇员的月薪与佣金之和*/

mysql> select *,sal+comm from emp;

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

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

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | NULL |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 1900.00 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 1750.00 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | NULL |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 2650.00 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | NULL |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | NULL |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | NULL |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | NULL |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 1500.00 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | NULL |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | NULL |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | NULL |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | NULL |

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

14 rows in set (0.00 sec)

mysql> /** comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：*/

mysql> select *,sal+ifnull(comm,0) from emp;

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

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

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 800.00 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 1900.00 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 1750.00 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 2975.00 |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 2650.00 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 2850.00 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 2450.00 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 3000.00 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 5000.00 |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 1500.00 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 1100.00 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 950.00 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 3000.00 |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 1300.00 |

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

14 rows in set (0.00 sec)

mysql> /**给列名添加别名：在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：*/

mysql> select *,sal+ifnull(comm,0) as total from emp;

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